Results 1 to 6 of 6
  1. #1
    Lounger
    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!

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 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

  3. #3
    Lounger
    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)

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 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
    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. #5
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where do I put Module?

    Quote Originally Posted by Maudibe View Post
    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. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 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 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
  •