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)

 

[Rick Rothstein]

As long as those number at the end are the only numbers in the cell, then this formula should work for you...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789"))-2)

 

[howtotype]

Brilliant stuff Rick, thanks. If there are other numbers in the cell is there a way to keep them as well?

 

[Rick Rothstein]

If there are other numbers in the cell is there a way to keep them as well?

Not using the formula I posted earlier as it looks for the first digit in the text to calculate from. As long as "other number" does not follow a dash and have its beginning digits equal to the number at the end (that is, you cannot have something like "party-123456time-1234" because the -1234 at the end matches the beginning of the "-123456" earlier in the text), then you can use this formula...

=IF(ISNUMBER(-RIGHT(A1)),SUBSTITUTE(A1,"-"&TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",99)),99),99)),""),A1)

 

[bschwartz]

Rick,

I love that repeat null text trick. What I don't get is the LEFT(RIGHT construction. They would seem to provide the same value, since the right is only returning 99 characters and the left is taking the first 99 of that 99, which is the whole of the RIGHT function. Am I missing something?

 

[Rick Rothstein]

Rick,

I love that repeat null text trick. What I don't get is the LEFT(RIGHT construction. They would seem to provide the same value, since the right is only returning 99 characters and the left is taking the first 99 of that 99, which is the whole of the RIGHT function. Am I missing something?

=IF(ISNUMBER(-RIGHT(A1)),SUBSTITUTE(A1,"-"&TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",99)),99),99)),""),A1)

You are right... for this particular application, the LEFT function call can be eliminated which shortens the formula to this...

=IF(ISNUMBER(-RIGHT(A1)),SUBSTITUTE(A1,"-"&TRIM(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",99)),99)),""),A1)

The part I highlighted in red above (in my originally posted formula) came from my mini-blog article here...

Get "Reversed" Field from Delimited Text String

That article gives a generalized formula for finding any delimited field in a text string counting the field from the back of the text forward (so a fieldnumber of 1 equates to the last delimited text field, not the first). When the fieldnumber is greater than 1, the LEFT function call is important... I never paid attention to the fact that when the fieldnumber is 1, the "LEFT(RIGHT(" combination degenerates into the same text characters, so thanks for pointing that out to me!

 

[bschwartz]

@ Rick,

I understand now. Thanks!

 

[howtotype]

Rick, that is superb. It works perfectly for this. Thanks so much!!

 

[smarty3010]

Hi,
I am trying to remove all characters except alphabets in one column.

My input is as follows:-

Rahul 1 Ashok More Neeta 1 K. Khandelwal 0 M. Pancholi Heena Kumari s/o Manchharan purohit Output needed:- Rahul Ashok More Neeta K. Khandelwal M. Pancholi Heena Kumari so Manchharan purohit <tbody> </tbody> <tbody> </tbody> <tbody> </tbody> <tbody> </tbody> <tbody> </tbody> <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

Hi,
I am trying to remove all characters except alphabets, periods and spaces in one column.

Given what your example showed you wanted, I assume what I added in green above would be what you meant to ask for. Assuming that is correct, give this macro a try...

Sub AlphasOnly()
  Dim R As Long, X As Long, Data As Variant, CellText As String
  Data = Range("[B][COLOR="#FF0000"][SIZE=2]A[/SIZE][/COLOR][/B]1", Cells(Rows.Count, "[B][COLOR="#FF0000"][SIZE=2]A[/SIZE][/COLOR][/B]").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
  Range("[B][COLOR="#0000FF"][SIZE=2]B[/SIZE][/COLOR][/B]1").Resize(UBound(Data)) = Data
End Sub

Note... you did not say what column your data was in so I assumed Column A (red text); nor did you say where you wanted the output to go to, so I assumed Column B (blue text)... change either or both of these to the column letter designation that matches your actual layout.


HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (AlphasOnly) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and 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.