I have data that appears like this: "R1-C2-D1-12". I would like the same data
appear in another column like this: "1-2-1-12". How can I make this happen
without spending hours typing it in, I have several thousand lines?

ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will strip out all the alpha characters............

Vaya con Dios,
Chuck, CABGx3



"gdd6936" wrote:

I have data that appears like this: "R1-C2-D1-12". I would like the same data
appear in another column like this: "1-2-1-12". How can I make this happen
without spending hours typing it in, I have several thousand lines?

Sub RemoveAlphas()
'' Remove alpha characters from a string.
'' except for decimal points and hyphens.
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 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


Gord Dibben MS Excel MVP

On Fri, 2 Jun 2006 12:01:02 -0700, gdd6936
wrote:

I have data that appears like this: "R1-C2-D1-12". I would like the same data
appear in another column like this: "1-2-1-12". How can I make this happen
without spending hours typing it in, I have several thousand lines?

On Fri, 2 Jun 2006 12:01:02 -0700, gdd6936
wrote:

I have data that appears like this: "R1-C2-D1-12". I would like the same data
appear in another column like this: "1-2-1-12". How can I make this happen
without spending hours typing it in, I have several thousand lines?

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then (assuming that all the letters will be capital letters), use the formula:

=REGEX.SUBSTITUTE(A1,"[A-Z]")

If there may be non-capitalized letters, then:

=REGEX.SUBSTITUTE(A1,"[A-Za-z]")


--ron