Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2000-split a cell or a column or a row without transferring | New Users to Excel | |||
Excel 2000 - Split Contents of Cell Across Multiple Cells | Excel Discussion (Misc queries) | |||
split a cell diagonally in excell - a calendar -2 dates in 1 cell | Excel Discussion (Misc queries) | |||
how can I split a single cell diagonally in Excel 2000 | Excel Discussion (Misc queries) | |||
Office 2000 excell sheet gives error message for cell color in of. | Excel Worksheet Functions |