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

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" wrote in
message ...

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
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