|
Split Cell Data using Excell 2000
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 |
Split Cell Data using Excell 2000
Just do a normal sort, numbers sort before text.
-- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "jfcby" 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 |
Split Cell Data using Excell 2000
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 On Wed, 30 Aug 2006 19:29:58 -0700, "Jim Cone" wrote: Just do a normal sort, numbers sort before text. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "jfcby" 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 |
Split Cell Data using Excell 2000
Hello Gord,
Thank you for your help! The code works great! jfcby Gord Dibben wrote: 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 On Wed, 30 Aug 2006 19:29:58 -0700, "Jim Cone" wrote: Just do a normal sort, numbers sort before text. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "jfcby" 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 |
| All times are GMT +1. The time now is 05:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com