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)
Printable View
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)
Hi
See if this helps you.
http://www.excelfox.com/forum/f13/ex...eric-text-186/
But I also want to separate the text away from the numbers
As it is located in the Sheet
up
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","" ))
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)...
HOW TO INSTALL UDFsCode: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
------------------------------------
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.
Define a Name via Name Manager
Select the first cell in your range (here I selected A2 on Sheet1)
Name: String
Refers to:In B2:PHP Code:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,1,""),2,""),3,""),4,""),5,""),6,""),7,"")
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(String,8,"" ),9,""),0,""))
Note: The dollar sign should be there berore the column name.
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).
THANK YOU Mr Admin
THANK YOU Mr Rick