Splitting complex string into 3 numbers with a formula - Please help!

[Jason]

Hi--

I have a string that is a unique ID number in the form XXX-XXX-XXX.
However, the number of digits in each segment of the string varies.
For example, all of the following are valid:
2-5895-223
1213-23-257676
55-1-2

I am trying to write a formula to split the string into three separate
numbers. Using the first example, the three numbers would be:
2
5895
223

I am having trouble writing a formula flexible enough to identify
where the dash is and to split the string in the right place. Any
suggestions?

Thanks,
Jason

 

[ward376]

Can you use text to columns with a dash as the delimiter?

Cliff Edwards

 

[David Biddulph]

Data/ Text to Columns/ Delimited/ Other: (use hyphen)

 

[Rick Rothstein \(MVP - VB\)]

Cliff's suggested Data/Text To Columns would be the most efficient method;
but. of course, it requires you to repeat it as new entries are made.
Assuming you want the splitting apart to be automatic, you can use the
following formulas for that. Assuming your first ID number is in A2 (with A1
being a header), put the following formula in the indicated cells and then
copy them all down as far as you expect to ever have to...

B1: =IF(A2="","",LEFT(A2,FIND("-",A2)-1))

C1: =IF(A2="","",SUBSTITUTE(SUBSTITUTE(A2,B2&"-",""),"-"&D2,""))

D1: =IF(A2="","",MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,99))

Rick

 

[Jason]

Rick, thanks so much! I am trying to use formulas so your method is
what I'm looking for.

It almost works, but I am getting some incorrect results in Column C.
Column B & D both work fine.
The following work fine in column C, for example:
1-10-11740
1-10-3780
1-246-42601
1-246-42602


But these original numbers are giving errors in column C:
A C
1-421-42101 --> 4242101
1-421-42102 --> 4242102
2-2-102889 --> 102889
2-2-10289 --> 10289
2-2-636 --> 636
2-2-8 --> 8

Any suggestions?

Thanks,
Jason

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I tried to take a short-cut and didn't think the problem all the way
through. Use this in B2 instead and then copy it down...

=IF(A2="","",MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1))

Rick

 

[Jason]

Perfect, thanks again!

Jason

Sorry, I tried to take a short-cut and didn't think the problem all the way
through. Use this in B2 instead and then copy it down...

=IF(A2="","",MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1))

Rick

 

[T. Valko]

D1: =IF(A2="","",MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,99))

A few keystrokes shorter:

=SUBSTITUTE(A2,B2&"-"&C2&"-","")

 

[Rick Rothstein \(MVP - VB\)]

After using my corrected formula for B2 posted elsewhere in this thread, of
course.<g>

Rick