Extract Only Numbers & TEXT From Text String

[mahmoud-lee]

I need to separate the numbers and the text of the existing text in column (A)
I need this solution by formulas, and use (Office 2003)

[Admin]

Hi

See if this helps you.

http://www.excelfox.com/forum/f13/ex...eric-text-186/

[mahmoud-lee]

But I also want to separate the text away from the numbers
As it is located in the Sheet

[mahmoud-lee]

up

[Admin]

Hi

To remove numbers, try
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(A2,"1",""),"2",""),"3",""),"4","") ,"5",""),"6",""),"7",""),"8",""),"9",""),"0","" ))

[mahmoud-lee]

Thank you mr admin , This formula is not working after the seventh condition

[Rick Rothstein]

Thank you mr admin , This formula is not working after the seventh condition

That is because you are using XL2003 which has a limit of 7 nested function calls (this limit was lifted in XL2007 and above). How about two UDFs (User Defined Functions)...

Function DigitsOnly(ByVal s As String) As Variant
  Dim X As Long
  For X = 1 To Len(s)
    If Mid(s, X, 1) Like "[!0-9]" Then Mid(s, X, 1) = Chr(1)
  Next
  DigitsOnly = Replace(s, Chr(1), "")
  If Len(DigitsOnly) < 16 Then DigitsOnly = Val(DigitsOnly)
End Function

Function NoDigits(ByVal s As String) As String
  Dim X As Long
  For X = 1 To Len(s)
    If Mid(s, X, 1) Like "#" Then Mid(s, X, 1) = Chr(1)
  Next
  NoDigits = Trim(Replace(s, Chr(1), ""))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, put the specified formula in the indicated cells...

B3: =DigitsOnly(A3)

C3: =NoDigits(A3)

and then copy them down.

[Admin]

Thank you mr admin , This formula is not working after the seventh condition

Define a Name via Name Manager

Select the first cell in your range (here I selected A2 on Sheet1)

Name: String

Refers to:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,1,""),2,""),3,""),4,""),5,""),6,""),7,"") 

In B2:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(String,8,"" ),9,""),0,""))

Note: The dollar sign should be there berore the column name.

[Rick Rothstein]

Another possible formula for returning only the digits is this one posted previously in another forum by Lars-Åke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10 ^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).

[mahmoud-lee]

THANK YOU Mr Admin
THANK YOU Mr Rick