Remove numbers from cell

EricB123 · January 13th, 2007, 01:11 AM

I have a list of 100 (A1:A100) names followed by numbers in the same cell. I would like to create a macro that will remove the numbers but will leave the name alone. For example:

John123 will be changed to John.
Pete12 will be changed to Pete.

Thanks for any help with this.

echovue · January 13th, 2007, 02:07 AM

Hi Eric

How familiar are you with VBA?

Mike
EchoVue.com

echovue · January 13th, 2007, 02:17 AM

If so, the quick and dirty way would be a sub like the following...

Code:

Public Sub RemoveNumbers()
    Dim cellText As String
    Dim i As Integer

    For i = 1 To 100

        Range("A" & i).Select

        'Get the text from the cell
        cellText = Selection.Text

        'As long as there is a number on the right, chop it off
        While IsNumeric(Right(cellText, 1))
            cellText = Left(cellText, Len(cellText) - 1)
        Wend
        'Write the new text string back into the cell
        Selection.Value = cellText
    Next

End Sub

Hope that helps, I'm calling it a night...

Mike

Mike
EchoVue.com

EricB123 · January 13th, 2007, 12:31 PM

Thanks a lot, that code worked perfectly.

echovue · January 13th, 2007, 12:33 PM

Glad to help! Welcome to the forum.

Mike
EchoVue.com

claralou · May 9th, 2008, 12:48 PM

Hi there

I need a similar code to this, only I need to delete numbers from the beginning of the cell, together with an underscore.

i.e. I want cell contents text: 219_ugo_italian_SW_hospital.wav
to change to: ugo_italian_SW_hospital.wav

Can you advise on what change is needed to the code above to make it work for me please?

Thanks
Claire

Shasur · May 9th, 2008, 07:39 PM

Hi clara

Please try the following:

Code:

Public Sub RemoveNumbers_FromLEft()
    Dim cellText As String
    Dim i As Integer

    For i = 1 To 100

        Range("A" & i).Select

        'Get the text from the cell
        cellText = Selection.Text

        'As long as there is a number on the left, chop it off
        While IsNumeric(Left(cellText, 1))
            cellText = Right(cellText, Len(cellText) - 1)
        Wend

        ' chop off the leftmost underscore
        If InStr(1, cellText, "_") Then
              cellText = Right(cellText, Len(cellText) - 1)
        End If

        'Write the new text string back into the cell
        Selection.Value = cellText
    Next

End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

stanleydgromjr · May 10th, 2008, 12:26 PM

claralou,

Here you go.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code:

Option Explicit
Sub RemoveNumbersUnderscore()
    Dim lngLastRow As Long
    Application.ScreenUpdating = False
    Range("A1").EntireColumn.Insert
    lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
    With Range("A1:A" & lngLastRow)
        .FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-FIND(""_"",RC[1],1))"
        .Copy
        .Value = .Value
        .Copy Range("B1")
    End With
    Columns("A:A").Delete Shift:=xlToLeft
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Then run the "RemoveNumbersUnderscore" macro.

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

Subs · June 3rd, 2010, 10:23 AM

Hi All,

I'm trying to carry out a similar task using Excel 2007. I have a column of numbers in the following format.

99.740% (99.875%)

I need to delete the first figure and the brackets so I end up with the bracketed figure but without the brackets. i.e. 99.875%

Can anyone help? Thanks in advance for you time!.

Subs

Shasur · June 3rd, 2010, 09:54 PM

Hi

You can use the combination of Mid, Instr and Left Functions

Code:

Sub Extract_Numbers_In_Bracs()

Dim oCell As Range
Dim Sval

For Each oCell In Range("A1:A5")
    If InStr(oCell.Value, "(") And InStr(oCell.Value, "(") Then
        Sval = Mid(oCell.Value, InStr(oCell.Value, "(") + 1, Len(oCell.Value))
        oCell.Value = Left(Sval, InStr(Sval, ")") - 1)
    End If
Next oCell

End Sub

Cheers
Shasur