Cell format
 

I have an issue I think can be solved with a Custom Cell Format. The problem
is I'm not sure how to write the format. I have a Name column formated as
General. The data looks like this in each cell: Smith1234, John
How do I reformat the cell to knock out the numbers and leave me with Smith,
John. Manually doing this would be consuming as I am trying to change about
4000 entries. Thanks in advance for any help!

Cell format
 

You can use a macro.
But in your case, you don't need to scan through all 4000 entries which could be time consuming. You can use another approach, which is to find and replace all numeric value in the cells. ie. from 0 to 9.

So, select the range of cells to clean up, and run the following macro.

Sub Macro1()
Dim tmp As Integer
With Selection
For tmp = 0 To 9
.Replace What:=tmp, Replacement:=""
Next
End With
End Sub

Cell format
 

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

On Tue, 13 Jan 2004 19:38:43 -0700, "N Sabo" wrote:

I have an issue I think can be solved with a Custom Cell Format. The problem
is I'm not sure how to write the format. I have a Name column formated as
General. The data looks like this in each cell: Smith1234, John
How do I reformat the cell to knock out the numbers and leave me with Smith,
John. Manually doing this would be consuming as I am trying to change about
4000 entries. Thanks in advance for any help!

Cell format
 

Just My 2 cents:

Sub FixName()
Dim Comma1 As String, StrChk As Variant, Cnt
Dim LR As Long
Application.ScreenUpdating = False
LR = ActiveCell.SpecialCells(xlLastCell).Row
Cnt = 1
Range("A1").Activate
Comma1 = InStr(1, ActiveCell.Value, ",")
Do
Do
StrChk = Mid(ActiveCell.Value, Comma1 - Cnt, 1)
If IsNumeric(StrChk) = True Then
Cnt = Cnt + 1
Else
ActiveCell.Value = Left(ActiveCell.Value, Comma1 -
Cnt) _
& " " & Mid(ActiveCell.Value, Comma1 + 2, Len
(ActiveCell.Value))
End If
Loop Until IsNumeric(StrChk) = False
ActiveCell.Offset(1).Activate
Cnt = 1
Comma1 = InStr(1, ActiveCell.Value, ",")
Loop Until ActiveCell.Row LR

End Sub

HTH


-----Original Message-----
I have an issue I think can be solved with a Custom Cell
Format. The problem
is I'm not sure how to write the format. I have a Name
column formated as
General. The data looks like this in each cell:
Smith1234, John
How do I reformat the cell to knock out the numbers and
leave me with Smith,
John. Manually doing this would be consuming as I am
trying to change about
4000 entries. Thanks in advance for any help!


.