I am trying to extract numbers from a string of text. For example, the data
I have a

AMHERST /IN: CK 623
CASA NINOS OF MONT/IN: CK 8996
MONT SCH OF MANHA REF:954
MONT SCH OF DOWNT REF:10136
THE MIAMI MONT SC REF:1070
RESTON MONT SCH REF:13926
DAKOTA MONT SCH REF:15069

What I want to extract is the following:

If there is a "CK" (check #), I want the number value in one column.
If there is a "REF:", I want the number value in another column.
As shown above, the numbers can be of varying length.
Can someone tell me how I can do this?

Assuming all of your data follows the same format as your examples, then
these formulas should work for you.

To extract the CK number:

=IF(ISERROR(FIND(" CK ",A1)),"",MID(A1,FIND(" CK ",A1)+4,1024))

To extract the REF number:

=IF(ISERROR(FIND("REF:",A2)),"",MID(A2,FIND("REF:" ,A2)+4,1024))

HTH,
Elkar


"Marie" wrote:

I am trying to extract numbers from a string of text. For example, the data
I have a

AMHERST /IN: CK 623
CASA NINOS OF MONT/IN: CK 8996
MONT SCH OF MANHA REF:954
MONT SCH OF DOWNT REF:10136
THE MIAMI MONT SC REF:1070
RESTON MONT SCH REF:13926
DAKOTA MONT SCH REF:15069

What I want to extract is the following:

If there is a "CK" (check #), I want the number value in one column.
If there is a "REF:", I want the number value in another column.
As shown above, the numbers can be of varying length.
Can someone tell me how I can do this?

In B1 =IF(ISNUMBER(SEARCH("CK",A1)),MID(A1,SEARCH("CK",A 1)+2,99),"")
In C1 =IF(ISNUMBER(SEARCH("REF",A1)),MID(A1,SEARCH("REF" ,A1)+4,99),"")

Copy down


"Marie" wrote:

I am trying to extract numbers from a string of text. For example, the data
I have a

AMHERST /IN: CK 623
CASA NINOS OF MONT/IN: CK 8996
MONT SCH OF MANHA REF:954
MONT SCH OF DOWNT REF:10136
THE MIAMI MONT SC REF:1070
RESTON MONT SCH REF:13926
DAKOTA MONT SCH REF:15069

What I want to extract is the following:

If there is a "CK" (check #), I want the number value in one column.
If there is a "REF:", I want the number value in another column.
As shown above, the numbers can be of varying length.
Can someone tell me how I can do this?