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(SUBST ITUTE(A1,1,),2,),3,),4,),5,)

In cell C1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(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
---------------
Jacob Skaria


"Vital_ar" wrote:

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.

Thank you Jacob Skaria. I have already wrote the macro and using it. Thanks
for this Nested Substitute

"Jacob Skaria" wrote:

Formula way: (using a helper column)
In cell A1 you have the text

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

In cell C1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(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
---------------
Jacob Skaria


"Vital_ar" wrote:

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.