Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parsing data | Excel Programming | |||
Text parsing - Extracting data from inconsistent data entry format. | Excel Programming | |||
Parsing Data with | Excel Discussion (Misc queries) | |||
Help With Parsing Data | Excel Discussion (Misc queries) | |||
Parsing Data | Excel Programming |