Extracting numbers from string of text
 

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?

Extracting numbers from string of text
 

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?

Extracting numbers from string of text
 

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?