Formula

  • Thread starter Thread starter singkit
  • Start date Start date
S

singkit

can you help me build a formula that will separate
numeric to aphabet
e.g.

column a columnB
4649876467 (e-mail address removed) (e-mail address removed)

column a - has 4649876467 (e-mail address removed) value
the email address must be put in column B.
 
My suggestion, based on the example provided, would be to
use Data > Text-to-Columns with space as your delimiter.
Otherwise, use:

=SUBSTITUTE(MID(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW
(INDIRECT("1:"&LEN(A1))),1)),0),1024)," ","")

Array-entered, meaning press ctrl/shift/enter.

HTH
Jason
Atlanta, GA
 
Copy the column to another column then run this macro on that column.

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP
 
Hi Gord!

I did not test your macro but does it still work on
something like this:

123 (e-mail address removed)

Biff
-----Original Message-----
Copy the column to another column then run this macro on that column.

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells (xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0- 9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP


Hello,

What if I had a value without spacing?

Thanks
Click to expand...

.
Click to expand...
 
Back
Top