# Thread: Pull Out Number if Cell Contains a Number

1. ## Pull Out Number if Cell Contains a Number

Hi, say I have a column of data that looks like this...

CR, zero No 9 RC83 (WRN C) GD
CR, zero No 9 RC83 (WRN D) GD
CR, zero No 9 MEN RC83 (WRN C) GD
CR, zero No 9 MEN RC83 (WRN D) GD

What formula could I use to pull out the 83 and put in an adjacent column. Say my data begins on a1 and thru a4... and the adjacent column was obviously b...

Thanks!

2. Just "83" or "RC83"?
Is there likely to be another number you want to extract?

This will do what you want, but it's limited to finding "83".
=IF(ISERR(FIND(83,A2)),"",83)

cheers, Paul

3. If the number varies but is always between "RC" and the "(" character, this will find any number:
=MID(A1,FIND("RC",A1)+2,FIND("(",A1)-FIND("RC",A1)-2)

4. mitch,

If your data is static with the number always 83 and/or the prefix "RC" consistent then both Paul's and unclehewie's formulas will function admirably. If your data is more dynamic with varying numbers and/or prefixes prior to those numbers then this is a more flexible solution

Here is a user defined function (UDF) which will allow you to select any prefix and return any number.

In a standard module, place the following code:
Code:
```Public Function Pull(rng As Range, prefix As String)
Dim s, term, x As Integer
s = Split(rng, " ")
x = Len(prefix)
Pull = ""
For Each term In s
If Left(term, x) = prefix Then
Pull = Right(term, Len(term) - x)
End If
Next term
End Function```
In the cell that is to return the value, place the following formula then copy down:
=Pull(rng, prefix)

where rng is the cell address of the string and prefix is the string that precedes the number you wish to extract.

In the image below, I have presented several scenarios and the results they produce

mitch.png

HTH,
Maud

5. ## Where do I put Module?

Originally Posted by Maudibe
mitch,

If your data is static with the number always 83 and/or the prefix "RC" consistent then both Paul's and unclehewie's formulas will function admirably. If your data is more dynamic with varying numbers and/or prefixes prior to those numbers then this is a more flexible solution

Here is a user defined function (UDF) which will allow you to select any prefix and return any number.

In a standard module, place the following code:
Code:
```Public Function Pull(rng As Range, prefix As String)
Dim s, term, x As Integer
s = Split(rng, " ")
x = Len(prefix)
Pull = ""
For Each term In s
If Left(term, x) = prefix Then
Pull = Right(term, Len(term) - x)
End If
Next term
End Function```
In the cell that is to return the value, place the following formula then copy down:
=Pull(rng, prefix)

where rng is the cell address of the string and prefix is the string that precedes the number you wish to extract.

In the image below, I have presented several scenarios and the results they produce

mitch.png

HTH,
Maud
Maud, awesome looking and dynamic... how do I insert the code into the module and if I do will it work on all worksheets in the workbook? I have several different sheets of data I need to do this with and I think it would work.

Thanks to all!

6. Mitch,

Yes, it will work on any sheet and any column. The code goes in a standard module. Here are the steps to do that.
1. While looking at the excel spreadsheet, press Alt-F11 and the VB Editor will open.
2. Click on Insert (Tool bar) > Module and a blank standard module window with a white background will open on the right.
3. Copy the code above then paste into the standard module. You can close the VB Editor if you like or keep it open for additional editing.
4. Back in the spreadsheet, enter the formula =Pull(rng, prefix) where rng is the referenced cell and prefix is the string prior to the number to be extracted. Then copy down:

Example: In cell B1 =Pull(A1,"RC") then copy down so B2 =Pull(A2,"RC"), B3 =Pull(A3,"RC"), etc.. You can reference any column for example J1 =Pull(A1,"RC") or between sheets such as cell sheet1 cell B1 =Pull(Sheet2!A1,"RC"). In this example the number is extracted from sheet2 cell A1 and resulted in cell B1 on the first sheet.

HTH,
Maud

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•