Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strip text from numbers (Excel 97 SR2)

    Hi! I'm wondering if there's a formula in Excel that will strip the text from an alphanumeric field and just leave me with a number. For example, this field can have an entry of RE1017 or R10181, etc., and we just want the result to be 1017, 10181, etc. The user doesn't want to do a find/Replace, she prefers to have a formula. Is there anything that can do this?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    This is not trivial - it involves advanced use of array formulas. The following is from array formula guru Bob Umlas, if I remember correctly.

    Let's say that the values are in column A, starting with A1. In cell B1, type the following formula:

    =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$10) ,1)),0),255)

    It must be entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. Fill down as far as needed.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Hans,

    Thanks, that works great! Now I just need to look at it for awhile to try and figure out exactly what the formula is doing [img]/forums/images/smilies/ohmy.gif[/img])

    -Brett

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Brett,

    If you're interested in how these things work, take a look at Using Array Formulas in Excel OFC-10. This is where I got the formula from. I must admit that these complicated array formulas always leave me <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    The formula of Bob's only works if you want to strip the BEGINNING text. It will fail ifnyou have text AFTER the number.
    If you have something like: "abc1234xy" with a NUMBER internal to text strings, this array will work:

    =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1 )),0),SUM(IF(ISNUMBER(1*MID(A1,ROW(1:20),1)),1)))

    Instead of getting the MID from the start number to 255 char, it counts the number of "numbers" in the string and ONLY grabs that many.

    If you have more than 20 total characters you should change the "1:20" to the number of digits, especially for the 2nd occurrence. The first "1:10" only matters for the initial string. The 2nd (1:20) needs to take into account the total lenght of initial text and all the Numbers.

    Steve

  6. #6
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Hi, I may be missing something. I need to do the following and have tried based on the post I am repling to and I can't get it to work.
    I need to strip out commas from variable length strings while maintaining leading zeros. For example 0,1,3,9,5 needs to be changed to 01395. Find and replace removes the leading zero. The strings are of different size (some 0,2 and 2,3,6 or 0,5,6,8. Basically I need to remove the commas and leave everything else.

    Thanks for the help,
    Steve

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    If the only text symbol you have is commas, and you want leading zeros with a variable length number, it may be easier to first format the column as text, and then Find-and-Replace commas (",") with nothing.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Believe it or not it doesn't work. I tried that and for some reason find and replace removes the leading zeros along with the commas. I even tried replacing the zeros with x's and then replacing the x's with zeros and it continued to strip out the leading zeros even though the cells were formatted as text. I assumed it would have been a sure thing but to no avail. It does work if you preceed the entire string with an ' and then try it and it also works if you replace the , with a space. It is a work around that I can live with but thought that formula may be the answer.

    Thanks for the suggestion...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Does this formula do what you want (for cell A1)?<pre>=SUBSTITUTE(A1,",","")</pre>


  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    See if this VBA macro will do what you want:

    <pre>Public Sub RemoveCommas()
    Dim oCell As Range
    For Each oCell In Range("A1", Range("A65536").End(xlUp))
    If (InStr(oCell.Value, ",") > 0) Then
    oCell.NumberFormat = "@"
    oCell.Value = Replace(oCell.Value, ",", "")
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Thanks Hans, works nicely...

  12. #12
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Thanks Legare,
    I am getting a compile error. Will debug. Am trying to learn VBA so will be good practice. Thanks for the reply.

    Steve

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Legare uses the Replace function in his macro. This function was introduced in Office 2000 VBA, it is not available in Office 97. You can use the worksheet function SUBSTITUTE instead:

    Public Sub RemoveCommas()
    Dim oCell As Range
    For Each oCell In Range("A1", Range("A65536").End(xlUp))
    If (InStr(oCell.Value, ",") > 0) Then
    oCell.NumberFormat = "@"
    oCell.Value = Application.WorksheetFunction.Substitute(oCell.Val ue, ",", "")
    End If
    Next oCell
    End Sub

  14. #14
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip text from numbers (Excel 97 SR2)

    Thanks Hans... This worked nicely. I did fail (my apologies) to mention in my original post that I am using Excel 2002, not that it matters at this point as these solutions solve my problem for which I am grateful. I posted to the 97 thread as it seemed to match my problem. Just forgot to put in a key ingredient.

    Thanks again...Steve

Posting Permissions

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