• Status: Solved
• Priority: Medium
• Security: Public
• Views: 253

# 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.
0
ITworks
2 Solutions

Commented:
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

Commented:
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

EngineerCommented:
Try

=LEFT(A1,FIND("-",A1&"-")-1)
=SUBSTITUTE(SUBSTITUTE(A1,B1,""),"-","")
0

Author Commented:
Thanks for such fast responses!   Both worked just fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.