Results 1 to 14 of 14

20030311, 17:18 #1
 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!

20030311, 17:28 #2
 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.

20030311, 19:12 #3
 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

20030311, 20:20 #4
 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 OFC10. 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>

20030311, 20:47 #5
 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

20030409, 18:08 #6
 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

20030409, 18:21 #7
 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 FindandReplace commas (",") with nothing.
John ... I float in liquid gardens
UTC 7ąDS

20030409, 19:24 #8
 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...

20030409, 19:30 #9
 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>

20030410, 01:31 #10
 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

20030410, 13:07 #11
 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...

20030410, 13:25 #12
 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

20030410, 13:33 #13
 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

20030410, 13:48 #14
 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