Results 1 to 6 of 6

20151030, 16:10 #1
 Join Date
 Jan 2013
 Posts
 41
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20151031, 02:41 #2
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 7,999
 Thanks
 46
 Thanked 954 Times in 885 Posts
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

20151031, 07:53 #3
 Join Date
 Feb 2011
 Posts
 28
 Thanks
 0
 Thanked 10 Times in 8 Posts
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)

20151031, 09:32 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
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
=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

20151031, 19:06 #5
 Join Date
 Jan 2013
 Posts
 41
 Thanks
 0
 Thanked 0 Times in 0 Posts

20151031, 19:50 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
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 AltF11 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