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.
LVL 4
ITworksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
ITW,

You can use IFERROR() to see if the splitting function returns an error. If it does, return either the full cell or a blank.

=IFERROR(LEFT(A1,FIND("-",A1)-1),A1)
and
=IFERROR(RIGHT(A1,LEN(A1)-FIND("-",A1)),"")

Matt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
You could use "Text to columns" functionality with "-" as a delimiter to split your values without using a formula..

..or if you want a formula then with data in A1 use this formula in B1

=TRIM(LEFT(A1,FIND("-",A1&"-")-1))

and this one in C1

=IF(ISNUMBER(A1+0),"",TRIM(REPLACE(A1,1,FIND("-",A1),"")))

If A1 is a single number like 305 then B1 will show 305 and C1 will remain blank

regards, barry
0
Saqib Husain, SyedEngineerCommented:
Try

=LEFT(A1,FIND("-",A1&"-")-1)
=SUBSTITUTE(SUBSTITUTE(A1,B1,""),"-","")
0
ITworksAuthor Commented:
Thanks for such fast responses!   Both worked just fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.