Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
I need a vba code to do what is similar to text-to-columns, to separate
numerals and alphabets. Thanks column(A) column(B) column(C) 2.85 AUD 2.85 AUD 2.85AUD 2.85 AUD AUD 2.85 AUD 2.85 AUD2.85 AUD 2.85 20090315 data 20090315 data 20090315data 20090315 data data 090315 data 090315 data090315 data 090315 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
Text-To-Columns would not be able to handle your data as it is possible for
there to be no space between your numbers and your text. Give this macro a try (change the assignments in the two Const statements to reflect your actual conditions)... Sub SplitCells() Dim X As Long Dim Z As Long Dim LastRow As Long Dim Word As String Dim Number As String Const StartRow As Long = 2 Const SheetName As String = "Sheet1" With Worksheets(SheetName) LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow With .Cells(X, "A") Number = "" Word = "" For Z = 1 To Len(.Value) If Mid(.Value, Z, 1) Like "[0-9.]" Then Number = Number & Mid(.Value, Z, 1) Else Word = Word & Mid(.Value, Z, 1) End If If Left(.Value, 1) Like "[0-9.]" Then .Offset(, 1).Value = Number .Offset(, 2).Value = Trim(Word) Else .Offset(, 1).Value = Trim(Word) .Offset(, 2).Value = Number End If Next End With Next End With End Sub -- Rick (MVP - Excel) "danpt" wrote in message ... I need a vba code to do what is similar to text-to-columns, to separate numerals and alphabets. Thanks column(A) column(B) column(C) 2.85 AUD 2.85 AUD 2.85AUD 2.85 AUD AUD 2.85 AUD 2.85 AUD2.85 AUD 2.85 20090315 data 20090315 data 20090315data 20090315 data data 090315 data 090315 data090315 data 090315 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
Hi, Rick
I got your idea. I make a little change to make work. Thank You very much. Here is my change Sub SplitCells() Dim X As Long Dim Z As Long Dim LastRow As Long Dim Word As String Dim Number As String Const StartRow As Long = 2 Const SheetName As String = "Sheet1" With Worksheets(SheetName) ' LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastRow = Range("A" & Rows.Count).End(xlUp).Row For X = StartRow To LastRow With .Cells(X, "A") Number = "" Word = "" For Z = 1 To Len(Cells(X, "A").Value) Range("B30") = .Value If Mid(Cells(X, "A").Value, Z, 1) Like "[0-9.]" Then Number = Number & Mid(Cells(X, "A").Value, Z, 1) Else Word = Word & Mid(Cells(X, "A").Value, Z, 1) End If ' If Left(.Value, 1) Like "[0-9.]" Then ' .Offset(, 1).Value = Number ' .Offset(, 2).Value = Trim(Word) ' Else ' .Offset(, 1).Value = Trim(Word) ' .Offset(, 2).Value = Number ' End If Next Z Cells(X, "B") = Number Cells(X, "C") = Trim(Word) End With Next X End With End Sub "Rick Rothstein" wrote: Text-To-Columns would not be able to handle your data as it is possible for there to be no space between your numbers and your text. Give this macro a try (change the assignments in the two Const statements to reflect your actual conditions)... Sub SplitCells() Dim X As Long Dim Z As Long Dim LastRow As Long Dim Word As String Dim Number As String Const StartRow As Long = 2 Const SheetName As String = "Sheet1" With Worksheets(SheetName) LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow With .Cells(X, "A") Number = "" Word = "" For Z = 1 To Len(.Value) If Mid(.Value, Z, 1) Like "[0-9.]" Then Number = Number & Mid(.Value, Z, 1) Else Word = Word & Mid(.Value, Z, 1) End If If Left(.Value, 1) Like "[0-9.]" Then .Offset(, 1).Value = Number .Offset(, 2).Value = Trim(Word) Else .Offset(, 1).Value = Trim(Word) .Offset(, 2).Value = Number End If Next End With Next End With End Sub -- Rick (MVP - Excel) "danpt" wrote in message ... I need a vba code to do what is similar to text-to-columns, to separate numerals and alphabets. Thanks column(A) column(B) column(C) 2.85 AUD 2.85 AUD 2.85AUD 2.85 AUD AUD 2.85 AUD 2.85 AUD2.85 AUD 2.85 20090315 data 20090315 data 20090315data 20090315 data data 090315 data 090315 data090315 data 090315 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
A second look at your original script, the only change that was actually
needed: from LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row to LastRow = Cells(.Rows.Count, "A").End(xlUp).Row "Rick Rothstein" wrote: Text-To-Columns would not be able to handle your data as it is possible for there to be no space between your numbers and your text. Give this macro a try (change the assignments in the two Const statements to reflect your actual conditions)... Sub SplitCells() Dim X As Long Dim Z As Long Dim LastRow As Long Dim Word As String Dim Number As String Const StartRow As Long = 2 Const SheetName As String = "Sheet1" With Worksheets(SheetName) LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow With .Cells(X, "A") Number = "" Word = "" For Z = 1 To Len(.Value) If Mid(.Value, Z, 1) Like "[0-9.]" Then Number = Number & Mid(.Value, Z, 1) Else Word = Word & Mid(.Value, Z, 1) End If If Left(.Value, 1) Like "[0-9.]" Then .Offset(, 1).Value = Number .Offset(, 2).Value = Trim(Word) Else .Offset(, 1).Value = Trim(Word) .Offset(, 2).Value = Number End If Next End With Next End With End Sub -- Rick (MVP - Excel) "danpt" wrote in message ... I need a vba code to do what is similar to text-to-columns, to separate numerals and alphabets. Thanks column(A) column(B) column(C) 2.85 AUD 2.85 AUD 2.85AUD 2.85 AUD AUD 2.85 AUD 2.85 AUD2.85 AUD 2.85 20090315 data 20090315 data 20090315data 20090315 data data 090315 data 090315 data090315 data 090315 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
A second look at your original script, the only change that was actually
needed: from LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row to LastRow = Cells(.Rows.Count, "A").End(xlUp).Row Why do you think that change is necessary? The "dot" in front of "Cells" makes it reference the worksheet set in the With statement... without the "dot", the reference is to the ActiveSheet which theoretically could be different from the worksheet referenced in the With statement. Put the "dot" back in. Cells(X, "B") = Number Cells(X, "C") = Trim(Word) Also put a "dot" in front of the above two Cells references that you added toward the end of the code (for the same reason). -- Rick (MVP - Excel) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
Hi, Rick
As is, your script won't work at all. Upon debugging, I found With the dot in the front as: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row My Excel returns LastRow = 1 (which is wrong) Without the dot in the front as: LastRow = Cells(.Rows.Count, "A").End(xlUp).Row My Excel returns LastRow = 9 (which is correct) As with script With .Cells(X, "A") The debugging cursor exited the sub right after script line For Z = 1 To Len(.Value) I can make the code running only without the dot i.e. With Cells(X, "A") Without any formal training, I just do it on trial and error basis. Please share your insight further. Thank "Rick Rothstein" wrote: A second look at your original script, the only change that was actually needed: from LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row to LastRow = Cells(.Rows.Count, "A").End(xlUp).Row Why do you think that change is necessary? The "dot" in front of "Cells" makes it reference the worksheet set in the With statement... without the "dot", the reference is to the ActiveSheet which theoretically could be different from the worksheet referenced in the With statement. Put the "dot" back in. Cells(X, "B") = Number Cells(X, "C") = Trim(Word) Also put a "dot" in front of the above two Cells references that you added toward the end of the code (for the same reason). -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns
Sorry, Rick
It was my gross mistake. I understand how the dot works now. Many thanks! "Rick Rothstein" wrote: A second look at your original script, the only change that was actually needed: from LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row to LastRow = Cells(.Rows.Count, "A").End(xlUp).Row Why do you think that change is necessary? The "dot" in front of "Cells" makes it reference the worksheet set in the With statement... without the "dot", the reference is to the ActiveSheet which theoretically could be different from the worksheet referenced in the With statement. Put the "dot" back in. Cells(X, "B") = Number Cells(X, "C") = Trim(Word) Also put a "dot" in front of the above two Cells references that you added toward the end of the code (for the same reason). -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Excel is automatically doing "text to columns" upon paste text. | Excel Discussion (Misc queries) | |||
Linking text columns with text and data columns | Excel Worksheet Functions |