macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column with mixed numbers and letters. The numbers are in the front of the cell with the text following. I need to delete the numbers and leave the text in tact (moved into the left of the cell with no spaces)

I thought I could create a macro to help me automate the process... but the macro I create only copies the original cell to the new cell with numbers deleted.

Any suggestions?
 
Are the numbers and letters in the same position? In other words, is the text always 4 characters from the right of the text?

012345abc
0123abc
012abc

If so, use =Right(cell,4) formula to just pull off just the alpha text.
 
Hi Brett,
Place the function and the subroutine into a regular module,
make your selection, and invoke the subroutine.

001abc004 --> abc
9345 --> (empty)
abc-def --> abc-def

If that is not what you want, you will have to explain more explicitly

The function and subroutine were adapted from material provided
in newsgroups by Harlan Grove.
Extraction of a Group of Digits and Dashes, posted by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm .

Function RemoveDigitsAll(ByVal s As String) As String
'based on Harlan Grove, worksheet.functions, 2003-10-20
'concatenate all non digits found in a string
Dim i As Long, n As Long
n = Len(s)
For i = 1 To n
If Mid(s, i, 1) Like "[0-8]" Then Mid(s, i, 1) = "9"
Next i
RemoveDigitsAll = Application.WorksheetFunction.Substitute(s, "9", "")
End Function

Sub LeaveNonDigits()
Dim cell As Range '2003-10-18 dmcritchie, misc, modified
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants))
On Error GoTo 0
If rng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants))
cell.Value = "'" & RemoveDigitsAll(cell.Value)
Next cell
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub


Brett said:
I have a column with mixed numbers and letters. The numbers are in the front of the cell with the text following. I need to
Click to expand...
delete the numbers and leave the text in tact (moved into the left of the cell with no spaces)
 
minor correction for efficiency, original works okay, change

For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants))
cell.Value = "'" & RemoveDigitsAll(cell.Value)
Next cell

to

For Each cell In rng
cell.Value = "'" & RemoveDigitsAll(cell.Value)
Next cell
 
Back
Top