# Thread: Strip text from numbers (Excel 97 SR2)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

8. ## 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. ## Re: Strip text from numbers (Excel 97 SR2)

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

10. ## 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>

11. ## Re: Strip text from numbers (Excel 97 SR2)

Thanks Hans, works nicely...

12. ## 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. ## 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. ## 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
•