Extracting Text only

[Guest]

I'm trying to extract text only from a column of cells with varying text and
number strings. Sometimes the cell starts with numbers, other times text.
There is no constant for the number at the beginning of the cells.

Assuming all data in column C.

Went through all the functions and couldn't find anything this simple. Or
maybe it's not that simple?

thanks!

 

[Gord Dibben]

Not so simple if data is mixed up as you state.

I would use this macro to strip out all the numbers.

Select the range in column C then run the macro.

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

 

[CLR]

ASAP Utilities, a free add-in available at www.asap-utilities.com includes
this capabaility among it's many nifty features.

Vaya con Dios,
Chuck, CABGx3