Split Cell Data using Excell 2000

[jfcby]

Hello,

I have a workbook with 75 worksheets 3 Columns and rows varies from 2
to 500. In column C my data looks like this:
1
1A
5B
15C
G-01
G-02
MC02
100CE
101CO
101CO
106CE
106CE
BOILER
CO103
CO108
MECH2
MECH
MECH
220CE
221C
203
226
CO214
300A
301C

Since I'm tring to sort Column C so that my numbers will be in numeric
order I need to move the letters with numbers into Column D which is
empty.

My questions is: How do I move the letters on the left or right side of
a number in column C to column D?

I've searched for a maro code to do this but I've been unsuccessful.

Thank you for your help,
jfcby

 

[Jim Cone]

Just do a normal sort, numbers sort before text.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"jfcby" <[email protected]>
wrote in message
Hello,
I have a workbook with 75 worksheets 3 Columns and rows varies from 2
to 500. In column C my data looks like this:
1
1A
5B
15C
G-01
G-02
MC02
100CE
101CO
101CO
106CE
106CE
BOILER
CO103
CO108
MECH2
MECH
MECH
220CE
221C
203
226
CO214
300A
301C

Since I'm tring to sort Column C so that my numbers will be in numeric
order I need to move the letters with numbers into Column D which is
empty.
My questions is: How do I move the letters on the left or right side of
a number in column C to column D?
I've searched for a maro code to do this but I've been unsuccessful.
Thank you for your help,
jfcby

 

[Gord Dibben]

jcfby

Copy Column C to Column D.

Select column D and run this macro.

Sub RemoveAlphas()
' Remove alpha 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 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

Sort on Column D.


Gord Dibben MS Excel MVP

 

[jfcby]

Hello Gord,

Thank you for your help! The code works great!

jfcby

jcfby

Copy Column C to Column D.

Select column D and run this macro.

Sub RemoveAlphas()
' Remove alpha 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 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

Sort on Column D.


Gord Dibben MS Excel MVP

Just do a normal sort, numbers sort before text.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"jfcby" <[email protected]>
wrote in message
Hello,
I have a workbook with 75 worksheets 3 Columns and rows varies from 2
to 500. In column C my data looks like this:
1
1A
5B
15C
G-01
G-02
MC02
100CE
101CO
101CO
106CE
106CE
BOILER
CO103
CO108
MECH2
MECH
MECH
220CE
221C
203
226
CO214
300A
301C

Since I'm tring to sort Column C so that my numbers will be in numeric
order I need to move the letters with numbers into Column D which is
empty.
My questions is: How do I move the letters on the left or right side of
a number in column C to column D?
I've searched for a maro code to do this but I've been unsuccessful.
Thank you for your help,
jfcby