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



On Jun 9, 12:20 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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" wrote in message

...

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