Remove numbers from cells with text

[teicfcl]

Hi,

I have copied information from an outside source to Excel. Unfortunately, the information includes numbers preceding the text which I need to delete for all records. Is there an easier way of deleting this information without going to each individual cell to delete the numbers?

Your assistance with this issue is greatly appreciated.

Thank you,
teicfcl

 

[mikerickson]

If the number of initial numeric characters is always the same, you could TextToColumns with a fixed width and not import the first column.

 

[teicfcl]

Hi mikerickson,

Thank you for such a timely response. Unfortunately, the numbers are in the same column as the text, therefore, there is not way to separate. Is there another way to remove the numbers without going through each individual cell?

Again thanks,
teicfcl
http://www.mrexcel.com/forum/member.php?u=73026

 

[sailepaty]

Can you post some samples of the cells you want to split?

Regards

 

[Brightspark98]

If there are the same amount of nubers you could try ...

=REPLACE(A10,1,7,"")

would change 1234567elephant to just elephant.

The 7 is the amount of characters to replace.

 

[mikerickson]

Hi mikerickson,

Thank you for such a timely response. Unfortunately, the numbers are in the same column as the text, therefore, there is not way to separate. Is there another way to remove the numbers without going through each individual cell?

Again thanks,
teicfcl
http://www.mrexcel.com/forum/member.php?u=73026

Have you tried text to columns.

Given a column

12abc
34def
56ghi

using the fixed with option, it would normaly split that one column into two

12 abc
34 def
56 ghi

But there is a section in the dialog that lets you not import the first column, resulting in

abc
def
ghi

It would be an appropriate tool if all the initial numbers have the same number of digits, as they do in this example.

 

[teicfcl]

Thank you all for getting back to me so quickly. This is the first time I have posted at this forum but extremely impressed with the responses. I will try all the suggestions and below is an example of the cells as per your request, sailepaty:

1 Questions only
12 Title of the questions
384 Just the questions

Again thanks and I will suggest this forum to family, friends and associates.

-teicfcl

 

[sailepaty]

Try this,

=REPLACE(A1,1,FIND(" ",A1),"")

Regards

 

[teicfcl]

Thank you sailepaty I will try that.

Have a great Sunday everyone!

-teicfcl

 

[mikerickson]

With that kind of data, you could

1)Add a helper column with the formula =SUBSTITUTE(A1," ","~",1)
2)Copy/PasteSpecial Values the helper column
3)TextToColumns with a ~ delimiter, not importing the first column
4)Delete the original column.

This macro automates that process

Sub test()
    Dim helperColumn As Range
    Dim relAdd As String, Delimiter As String, qDelim As String, formString As String
    
    With ThisWorkbook.Sheets("sheet1").Range("A:A"): Rem adjust
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            Delimiter = Chr(5)
            qDelim = Chr(34) & Delimiter & Chr(34)
    
            relAdd = .Cells(1, 1).Address(False, False, xlR1C1, , .Offset(0, .Parent.UsedRange.Columns.Count))
        
            formString = "AND(48<=CODE(" & relAdd & "),CODE(" & relAdd & ")<=57)"
            formString = "=IF(" & formString & ",SUBSTITUTE(TRIM(" & relAdd & "),"" ""," & qDelim & ",1)"
            formString = formString & ", " & qDelim & "&TRIM(" & relAdd & "))"
            
            Set helperColumn = .Offset(0, .Parent.UsedRange.Columns.Count)
            
            helperColumn.FormulaR1C1 = formString
            .Value = helperColumn.Value
            helperColumn.Delete shift:=xlToLeft
            
            .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Delimiter, FieldInfo:=Array(Array(1, 9), Array(2, 1))
        End With
    End With
End Sub