Parse values either side of a delimeter.

[Yappa]

Hi all,

I know this is probably an easy one, but i can't find an answer.

In cell A1 I have these possible values:

2-10
2-8
22-8
22-10

The actual numbers vary, but there are 4 possible length scenarios, and the delimeter is always "-".

What I am trying to achieve is to have the number before the "-" character appear in cell B1, and also have the number which appears after the "-" sign go into C1.
I've looked at LEFT, RIGHT and MID functions, aswell as LEN and SEARCH and FIND, but I just can't make these work together.

If anyone could help solve this for me, I can then hopefully have a base to work from for future problems.

Thanks,

Yappa.

[martindwilson]

=LEFT(A1,FIND("-",A1)-1) in b1
and
=MID(A1,FIND("-",A1)+1,255) in c1

[Yappa]

Thanks martindwilson, that works great.

Could you please explain what the -1 at the end of the 1st equation does, and also the +1,255 at the end of the 2nd equation does?

Yappa

[martindwilson]

split the formula down
=LEFT(a1,2) returns the first 2 characters from the left but if your example was 2-46 it would return 2-
FIND("-",A1) returns the position of the dash as a number which would be 2 so substitute this in the left() formula to replace the 2 then minus 1 so it always returns number of characters one les than the position of -
in this case 1
effectively
=LEFT(a1,("location of dash minus 1))
for the second you have to add one as you're working fron left to right so you want to return characters starting after the position of -
the 255 is just the number of characters to return you could just put 2 or whatever if the characters length after dash is fixed .

[Yappa]

Thanks martindwilson, really appreciate you not only supplying a solution, but also an explanation.

Yappa