Link to home
Start Free TrialLog in
Avatar of Sue Taylor
Sue TaylorFlag for United States of America

asked on

Formula Help

I need help with a formula to separate information.  Sometimes there will be a range of numbers that has a dash and sometimes there will not be a range.
My raw data looks like this:
91 - 99
305
305
82
95
93.3
93.2
89
90 - 94
95 - 97
87.7-89.2
88.6-89.5
83
95-97
88.5-89.5
88.5-89.2
92.7-93.3
87.1-88.3
92.3-93
91.7-92.2
91.5-91.6
91.4 - 93.2

I want it to look like this:

91        99
305      
305      
82      
95      
93.3      
93.2      
89      
90        94
95        97
87.7      89.2
88.6      89.5
83      
95      97
88.5      89.5
88.5      89.2
92.7      93.3
87.1      88.3
92.3      93
91.7      92.2
91.5      91.6
91.4        93.2


I know how to create a formula to find the "-"   =LEFT(A1,FIND("-",A1,1)-1)  or  =RIGHT(A1,LEN(A1)-FIND("-",A1)) but I don't know how to do the IF statement, etc. when there's not a dash.
ASKER CERTIFIED SOLUTION
Avatar of mvidas
mvidas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try

=LEFT(A1,FIND("-",A1&"-")-1)
=SUBSTITUTE(SUBSTITUTE(A1,B1,""),"-","")
Avatar of Sue Taylor

ASKER

Thanks for such fast responses!   Both worked just fine.