How to remove numbers from a cell leaving text only

[howtotype]

Hi there. First time poster here, long time browser! I'm trying to remove numbers from the end of a column of cells in Excel 2010, leaving just the text. The numbers can be of varying lengths but are always on the right of the string. Here's an example of the data I'm trying to clean up:

del-capo-1188144

dan-jones-1078

party-time

the-basil-118120 donkey-hotay

force-118816

privateer-7754

survey-1188132 I'd like it to end up as: del capo dan jones party time the basil donkey hotay force privateer survey <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

I can find and replace the hyphens with nothing if required, so they're not too big an issue. I just can't get rid of the numbers. From another post I found, the following formula removed the text from the cells, leaving just the numbers, but that's the opposite of what I was after.
=SUMPRODUCT(MID(0&A10,LARGE(INDEX(ISNUMBER(--MID(A10,ROW(INDIRECT("1:"&LEN($A$2))),1))*ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

 

[smarty3010]

Thanks a lot for the quick reply and a neat explanation. It worked perfectly. (Sorry for the extra spaces in the above post)

 

[snowyday]

Hey Rick,

This is some fantastic code, but I'm looking for one small tweak. I would like to apply the macro to a column of my choosing, not always A1 to B1.

I would like to select any column and replace the text in each cell with the character string only, no numbers. Is that straightforward?

Thanks!

 

[Rick Rothstein]

Hey Rick,

This is some fantastic code, but I'm looking for one small tweak. I would like to apply the macro to a column of my choosing, not always A1 to B1.

I would like to select any column and replace the text in each cell with the character string only, no numbers. Is that straightforward?

If I understand you correctly, you would want to select the column to process and have the code put the fixed text back into the cells they came from. If that is correct, then give this macro a try. Note, it will only work if you select the entire column. I can remove that restriction if you want, but I felt it added a small measure of safety since once the macro changed your data, you would not be able to undo that change (and the original data would be lost).

[table="width: 500"]
[tr]
	[td]Sub AlphasOnly()
  Dim R As Long, X As Long, Data As Variant, CellText As String
  If Selection.Rows.Count = Rows.Count Then
    Data = Range(Selection(1), Cells(Rows.Count, Selection.Column).End(xlUp))
    For R = 1 To UBound(Data)
      CellText = Data(R, 1)
      For X = 1 To Len(CellText)
        If Mid(CellText, X, 1) Like "[!A-Za-z. ]" Then Mid(CellText, X) = Chr(1)
      Next
      Data(R, 1) = Application.Trim(Replace(CellText, Chr(1), ""))
    Next
    Selection(1).Resize(UBound(Data)) = Data
  End If
End Sub[/td]
[/tr]
[/table]

 

[snowyday]

Wow, that's excellent, worked perfectly, exactly what I needed. Thanks so much for the fast response! Have a great day

 

[Sak_Guest12]

I want to remove number from text leaving which are present in between words.
e.g., Remov1e 123 Numbers012 from 1text

Output : Remov1e Numbers012 from 1text

Any formula or VB script code will help?

 

[Rick Rothstein]

I want to remove number from text leaving which are present in between words.
e.g., Remov1e 123 Numbers012 from 1text

Output : Remov1e Numbers012 from 1text

Any formula or VB script code will help?

Give this UDF (user defined function a try)...

Function RemoveNumbers(S As String) As String
  Dim X As Long, Words() As String
  Words = Split(S)
  For X = 0 To UBound(Words)
    If Not Words(X) Like "*[!0-9]*" Then Words(X) = ""
  Next
  RemoveNumbers = Application.Trim(Join(Words))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use RemoveNumbers just like it was a built-in Excel function. For example,

=RemoveNumbers(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.