how to remove number from alphanumeric cell

[Igneshwara reddy]

Hi,

I have few rows which are alphanumeric cells, I need to extract only the
numbers from the cell.

For Eg: One cell contains fjdslfdslflsd455646sdfds768468
Second cell contains fsdfsd4879899 fsdfdsf547555sdfds797988.

From this two cells I need to extract only numbers.

 

[CLR]

ASAP Utilities, a free add-in available at www.asap-utilities.com has
features that will do this for you.........

Vaya con Dios,
Chuck, CABGx3

 

[Gary''s Student]

Function numit2(r As Range) As String
Dim s As String, s2 As String, c As String
s2 = ""
s = r.Value
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c Like "#" Then
s2 = s2 & c
End If
Next
numit2 = s2
End Function

use like
=numit(A1)

 

[Ron Rosenfeld]

Hi,

I have few rows which are alphanumeric cells, I need to extract only the
numbers from the cell.

For Eg: One cell contains fjdslfdslflsd455646sdfds768468
Second cell contains fsdfsd4879899 fsdfdsf547555sdfds797988.

From this two cells I need to extract only numbers.

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

You can then use this formula:

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

fjdslfdslflsd455646sdfds768468 455646768468
fsdfsd4879899 fsdfdsf547555sdfds797988 4879899547555797988



which will remove all of the non-numeric characters from the string.

If you need to extract the numbers in groups, as they are separated in the
string, then use this formula:

=REGEX.MID($A1,"\d+",COLUMNS($A:A))

and copy/drag across as far as required for the number of groups.

fjdslfdslflsd455646sdfds768468 455646 768468

fsdfsd4879899 fsdfdsf547555sdfds797988 4879899 547555 797988



--ron