Sue Taylor
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.
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for such fast responses! Both worked just fine.
=LEFT(A1,FIND("-",A1&"-")-
=SUBSTITUTE(SUBSTITUTE(A1,