Extract Only Number From A String

Dear all,

In Excel, is there any function that I can get number only in string that has number & text

Ex: in cell A1 589as56
I would like to get the string 58956 only

Thank you very much

Nam

Hi Nam,

Try:

'=============>>
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = CLng(.Replace(sStr, vbNullString))
End With
End Function
'<<=============

A1: 589as56
B1 = DigitsOnly(A1) ==> 58956


---
Regards,
Norman



"lehainam" <lehainam.28ee8y_1148618701.4952@excelforum-nospam.com> wrote in
message news:lehainam.28ee8y_1148618701.4952@excelforum-nospam.com...
>
> Dear all,
>
> In Excel, is there any function that I can get number only in string
> that has number & text
>
> Ex: in cell A1 589as56
> I would like to get the string 58956 only
>
> Thank you very much
>
> Nam
>
>
> --
> lehainam
> ------------------------------------------------------------------------
> lehainam's Profile:
> http://www.excelforum.com/member.php...o&userid=18615
> View this thread: http://www.excelforum.com/showthread...hreadid=545735
>

or try this 1 change 100 rows
mark the first 1 and run macro
numbers stay and rest disaper

Sub koverter()

Dim i, j, x, Indhold, tal

For i = 1 To 100
Indhold = ActiveCell.Value
x = Len(Indhold)
For j = 1 To x
If IsNumeric(Mid(Indhold, j, 1)) Then tal = tal & Mid(Indhold, j, 1)
Next
ActiveCell.Offset(0, 0).Value = tal
tal = ""
ActiveCell.Offset(1, 0).Activate
Next
ActiveCell.End(xlUp).End(xlUp).Activate

End Sub

by the way, vhy cant i start a new question?
i tryed click on New and select Question, but nothing happens?

On Thu, 25 May 2006 23:44:26 -0500, lehainam
<lehainam.28ee8y_1148618701.4952@excelforum-nospam.com> wrote:

>
>Dear all,
>
>In Excel, is there any function that I can get number only in string
>that has number & text
>
>Ex: in cell A1 589as56
>I would like to get the string 58956 only
>
>Thank you very much
>
>Nam

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ (which will also give you acess to many other useful
functions) and then use this Regular Expression formula (with your string in
A1):

=REGEX.SUBSTITUTE(A1,"\D")

The formula substitutes <nothing> for everything in A1 that is not a digit.


--ron