software

I am trying to extract only the numbers from an alphanumeric field. The
numbers are not consistently in the same spot. Example: abc123, 123abc or
a123bc... I want a returned value of quot;123quot;.

Thank you
Hi Brian,

You could use this UDF (User defined Function):

' ================================================== ===========================

Function StripTxt(a As String) As String

' Niek Otten, March 22 2006

' Strips all non-numeric characters from a string, but leaves any decimal separator

' Returns a string, not a number!

' If you need a number, use =Value(StripTxt(...))
Dim i As Long

Dim b As String

For i = 1 To Len(a)

b = Mid$(a, i, 1)

If ((Asc(b) gt; 47 And Asc(b) lt; 58) Or b = Application.DecimalSeparator) Then StripTxt = StripTxt b

Next i

End Function

' ================================================== ===========================
If you don't know (yet) how to implement a UDF:
================================================

Pasting a User Defined Function (UDF)

Niek Otten, March 31, 2006
If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these steps:
Select all the text of the function. CTRL C (that is, press and hold down the CTRL key, press C, release both). This a shortcut
for Copy.

Go to Excel. Press ALT F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).

From the menu bar, choose Insertgt;Module. There should now be a blank module sheet in front of you. Click in it and then press
CTRL V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.

Press ALT F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)

================================================

--
Kind regards,

Niek Ottenquot;Brianquot; gt; wrote in message ...
gt;I am trying to extract only the numbers from an alphanumeric field. The
gt; numbers are not consistently in the same spot. Example: abc123, 123abc or
gt; a123bc... I want a returned value of quot;123quot;.
gt;
gt; Thank you
gt;
gt;
On Mon, 3 Apr 2006 11:01:02 -0700, Brian gt;
wrote:

gt;I am trying to extract only the numbers from an alphanumeric field. The
gt;numbers are not consistently in the same spot. Example: abc123, 123abc or
gt;a123bc... I want a returned value of quot;123quot;.
gt;
gt;Thank you
gt;

Download and install Longre's free morefunc.xll add-in from
xcell05.free.fr/

Then use the Regular Expression formula:

=REGEX.SUBSTITUTE(A1,quot;\Dquot;)

The expression quot;\Dquot; matches anything in the string that is not a digit [0-9].
The substitute function substitutes the non-digits with a null, leaving only
digits.
--ron