Parse Strings of Space-Separated Text of Varying Lengths

[mlexcelhelpforum]

Hello,

The other day TMShucks helped me to write a macro to parse text strings with several delimiters into other columns.

Now I'm trying to parse some more text strings, but which are separated only by spaces. Unfortunately I can't seem to figure out how to make the code work. Here's how I started:

Faulty

Please Login or Register  to view this content.

Basically all the code did was to copy the unparsed text into the neighboring column.

This is a sample of what I want to happen. (The number of characters in each row separated by spaces will vary, and there will be many more rows of data.):

What I have now
Col 1
Row 1: 一
Row 2:丁*丂*七*丄*丅*丆
Row 3:万*丈*三*上*下*丌*亐*卄
Row 4:不*与*丏*丐*丑*丒*专*丗
Row 5:且*丕*世*丘*丙*业*丛*东*丝
Row 6:丞*丟*丠*両*丢
Row 7:丣*两*严*丽
Row 8:並*丧

What I want
Col 1
Row 1: 一
Row 2:丁
Row 3:万
Row 4:不
Row 5:且
Row 6:丞
Row 7:丣
Row 8:並

Col 2
Row 1:
Row 2:丂
Row 3:丈
Row 4:与
Row 5:丕
Row 6:丟
Row 7:两
Row 8:丧

Col 3
Row 1:
Row 2:七
Row 3:三
Row 4:丏
Row 5:世
Row 6:丠
Row 7:严
Row 8:

Etc., with the total number of columns depending on the row with the most characters.

Then, Step 2, ultimately, I'd like to put all of those columns into one big column. So the whole process would parse the characters out of their cells and all into one big column, the order being unimportant. Like this, for example:

Col 1
Row 1: 一
Row 2:丁
Row 3:万
Row 4:不
Row 5:且
Row 6:丞
Row 7:丣
Row 8:並
Row 9:丂
Row 10:丈
Row 11:与
Row 12:丕
Row 13:丟
Row 14:两
Row 15:丧

Etc.

Thank you for your insight!

ML

[DonkeyOte]

Can you post a small sample data set in a file ?

I suspect your "space" is not a space character - as implied by the text above (note use of * in your sample text)

If we assume "space" is 2nd char in your 2nd string what does:

Please Login or Register  to view this content.

return ?

[mlexcelhelpforum]

Hello DonkeyOte,

My space is a space character but was transformed into an asterisk when posted to Excel Forum. It must have something to do with the Chinese characters.

I'm not sure where I should put the code you suggested?

I've attached a sample.

Thank you kindly,
ML

[snb]

i'd suggest:

Please Login or Register  to view this content.

if that doesn't work:

Please Login or Register  to view this content.

[mlexcelhelpforum]

Hello again,

Well, I don't know why I couldn't get it to work before, but now using "Text to Columns" seems to be working just fine to separate the data into columns. So Step 1 is solved!

Step 2 is to combine all of those new columns into one column. I'm sure I'll find a macro for this somewhere!

Thanks a lot,
ML

[snb]

Everything in 1 step

Please Login or Register  to view this content.

[mlexcelhelpforum]

Hi there snb,

I gave that a try, but it only took the spaces out.

I need each character to have its own cell in the column.

ML

[snb]

Please Login or Register  to view this content.