To remove numbers in a cell with out using the macro

  • Thread starter Thread starter Vital_ar
  • Start date Start date
V

Vital_ar

Dear All,
Consider a cell in which both Alphabet & numbers. I want to remove only the
number from that cell. I have don this using the SUBSTITUTE Function. But i
have to write the 10 subtitute fucntion in ten different cells. Is there any
other method to do it.
For example, in the cell a1, has the following text
abv1234111f5ds4fsd54fds54fsdf4ds6111111111119802
Formula which i used in b1 to K1 cells
=SUBSTITUTE(A1,1,"") and the answer is abv234f5ds4fsd54fds54fsdf4ds69802
=SUBSTITUTE(B1,2,"") and the result is abv34f5ds4fsd54fds54fsdf4ds6980 like
this i used this formula.
Is there is any other option to do this process in a single cell.
Thanks in advance.
 
Formula way: (using a helper column)
In cell A1 you have the text

In cell B1 (helper column)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,),2,),3,),4,),5,)

In cell C1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,6,),7,),8,),9,),0,)

UDF: Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

=RemoveNums(A1)

Function RemoveNums(strData As String) As String
Dim intTemp As Integer
For intTemp = 1 To Len(strData)
If Not IsNumeric(Mid(strData, intTemp, 1)) Then _
RemoveNums = RemoveNums & Mid(strData, intTemp, 1)
Next
End Function

If this post helps click Yes
 
Dear All,
Consider a cell in which both Alphabet & numbers. I want to remove only the
number from that cell. I have don this using the SUBSTITUTE Function. But i
have to write the 10 subtitute fucntion in ten different cells. Is there any
other method to do it.
For example, in the cell a1, has the following text
abv1234111f5ds4fsd54fds54fsdf4ds6111111111119802
Formula which i used in b1 to K1 cells
=SUBSTITUTE(A1,1,"") and the answer is abv234f5ds4fsd54fds54fsdf4ds69802
=SUBSTITUTE(B1,2,"") and the result is abv34f5ds4fsd54fds54fsdf4ds6980 like
this i used this formula.
Is there is any other option to do this process in a single cell.
Thanks in advance.
Click to expand...


You can do this easily with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=RemDigits(A1)

in some cell.

=============================
Option Explicit
Function RemDigits(s As String) As String
Dim re As Object
Const sPat As String = "\d+"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
RemDigits = re.Replace(s, "")
End Function
===============================
--ron
 
Thank you Jacob Skaria. I have already wrote the macro and using it. Thanks
for this Nested Substitute
 
Back
Top