Parsing Data
 

Good morning --

I'm trying to parse this:

A B C D
Alfred E Newman

into:

A B C D
Alfred E Newman

What I've written is:

Cells(iR, 1).Parse Destination:=Cells(iR, 2)

but it just copies the entire cell contents to the next row over. What am I
doing wrong?

TIA

Parsing Data
 

I think you will have more luck using TextToColumns, with a space as the
delimiter.


"pdberger" wrote:

Good morning --

I'm trying to parse this:

A B C D
Alfred E Newman

into:

A B C D
Alfred E Newman

What I've written is:

Cells(iR, 1).Parse Destination:=Cells(iR, 2)

but it just copies the entire cell contents to the next row over. What am I
doing wrong?

TIA

Parsing Data
 

TextToColumns might be easier to use, but this will do what you asked using
the Parse method (where I commented in the assumed For-Next loop I assume
you are using)...

Dim X As Long
Dim iR As Long
Dim Pattern As String
Dim Words() As String

'For iR = <StartRow to <EndRow
Words = Split(Cells(iR, 1).Value)
Pattern = "["
For X = 0 To UBound(Words)
Pattern = Pattern & Space(Len(Words(X))) & "]"
If X < UBound(Words) Then Pattern = Pattern & " ["
Next
Cells(iR, 1).Parse Destination:=Cells(iR, 2), ParseLine:=Pattern
'Next

--
Rick (MVP - Excel)


"pdberger" wrote in message
...
Good morning --

I'm trying to parse this:

A B C D
Alfred E Newman

into:

A B C D
Alfred E Newman

What I've written is:

Cells(iR, 1).Parse Destination:=Cells(iR, 2)

but it just copies the entire cell contents to the next row over. What am
I
doing wrong?

TIA

Parsing Data
 

JMB --

Works great! Less filling!

Thanks!

"JMB" wrote:

I think you will have more luck using TextToColumns, with a space as the
delimiter.


"pdberger" wrote:

Good morning --

I'm trying to parse this:

A B C D
Alfred E Newman

into:

A B C D
Alfred E Newman

What I've written is:

Cells(iR, 1).Parse Destination:=Cells(iR, 2)

but it just copies the entire cell contents to the next row over. What am I
doing wrong?

TIA

Parsing Data
 

Just be aware that TextToColumns inherits previous settings (whether from
code or from the Data/TextToColumns menu bar at the worksheet level). For
example, put this...

AAA,BBB,CCC

in A1 and then use a comma delimiter in Data/TextToColumns from the menu
bar. Now change the entry in A1 to this...

XXX, YYY, ZZZ

and run this macro...

Sub Test()
Range("A1").TextToColumns Destination:=Range("B1"), _
DataType:=xlDelimited, Space:=True
End Sub

Notice that TextToColumns retained the previously set comma delimiter and
simply added the space delimiter to the mix (thus producing the skipped
column). Now select Data/TextToColumns from the menu bar and click the Next
button once and look at the selected delimiters. Notice that both the comma
**and** the space are checked off.... the setting implemented from code has
"stuck" at the worksheet level. Just something to keep in mind when using
TextToColumns... you may want to "clean" things up before/after you use it.

--
Rick (MVP - Excel)


"pdberger" wrote in message
...
JMB --

Works great! Less filling!

Thanks!

"JMB" wrote:

I think you will have more luck using TextToColumns, with a space as the
delimiter.


"pdberger" wrote:

Good morning --

I'm trying to parse this:

A B C D
Alfred E Newman

into:

A B C D
Alfred E Newman

What I've written is:

Cells(iR, 1).Parse Destination:=Cells(iR, 2)

but it just copies the entire cell contents to the next row over. What
am I
doing wrong?

TIA