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

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

Splitting complex string into 3 numbers with a formula - Pleasehelp!
 

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

Cliff Edwards

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

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

"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

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

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

Splitting complex string into 3 numbers with a formula - Pleasehelp!
 

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

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

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" wrote in message
...
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

Splitting complex string into 3 numbers with a formula - Pleasehelp!
 

Perfect, thanks again!

Jason

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

...

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

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

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

A few keystrokes shorter:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

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

Rick


"T. Valko" wrote in message
...
D1: =IF(A2="","",MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,99))

A few keystrokes shorter:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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