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

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

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

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

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)

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)

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)