Extract Only Number From A String

[lehainam]

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

 

[Norman Jones]

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

 

[Guest]

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?

 

[Ron Rosenfeld]

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