Replace multiple instances of spaces with a single delimiter

I tried that, but it divides all the data up even when it's just separated by a single space.

YES! That works beautifully; it's awesome! Thank you so much! (I changed the "bar" to a comma, just because comma or tab are the delimiters I'm used to seeing.)

Now the NEW problem I've discovered is that there are more inconsistencies in the number of spaces within a line of data than I thought, so the text-to-columns is not always leaving things lined up correctly.

I'll re-attach the file with a tab showing how it looks after the macro and Text-To-Columns.

For example - In the first row of data (row 25):

• There are multiple spaces between "LCS18" and "TWWJ3", so the macro is putting a comma there when it doesn't need one.
• Also in the first row there's only 1 space between "TWWJ3" the size ("Mediu") so it's NOT getting a comma when it needs one.
• In the first 2 rows there's only 1 space before "VS" so there's not a comma going in there when it needs one.
• But, in the 3rd and 4th rows, there are multiple spaces before "VS" so the macro is correctly putting a comma in there.
• In all of the rows, there's only 1 space after the digit after the color so those aren't getting a comma but they should

I'm afraid this report might be exporting in such a ridiculously chaotic manner that it may be impossible to get it organized correctly without an act of God or something, LOL!
Meanwhile, we're checking with the person that creates the report in the first place to see if it can be exported directly as a .csv; hopefully that would make it at least START OUT like a normal file.

Anyway, thank you so much for your coding! I'm sure I'll still be able to use it, if not here, then in another project. (I haven't given up on this one yet, though).

Have a great day!

Jenny

I copied your original data block into Notepad and changed the font to Courier. The incoming text is position aligned. You may want to try handling it as you would an incoming prn file or use the mid function to put each "section" of the string into a separate cell.

See attached image.

TextColumns.jpg

Hmmm, that's interesting! I wonder why changing it to Courier makes the whole layout change like that.

I don't know anything at all about prn files; I'll have to Google that. I'm about to leave for the day, but I'll look at this some more tomorrow.

Thanks for the ideas and I'll come back tomorrow and let you know if I got anywhere, LOL!

Jenny

I was in a rush yesterday or else I would have tried the Text to Columns using the "fixed width" option. Just tried it now, it works great in defining your columns. Good luck.

Isn't it wonderful when the light bulb comes on!

I do this quite frequently with text files. I'm not saying my method is the best one but it's been working for me.

Here's how you would extract the pieces of the string and assign them to the new cell locations:

Please Login or Register  to view this content.

Instead of "value" you can also use "text" but I believe you have prices and sales etc that would need the value option.
You may want to format each cell at the same time you are populating it.

One more thing I forgot to mention. If you don't want those trailing blanks to be in your new cell location, precede the "Left" or "Mid" functions with the "Trim" function.

That looks like it's going to work for that problem very nicely!

But, in scrolling through the 5,000+ row report, I've noted 1 or 2 other anomalies -

First:The 2 blocks containing "Name, Address, City, State, Zip, Phone #" is not organized the same on every single record. There only appear to be 2 formats, though.
There's a "Sold To" block and a "Ship To" block.

• On some records, the "Sold To" block is on 1 set of rows and the "Ship To" block is a couple of rows below it. (See the top part of Sheet1 on the attachment)
• On other records, the 2 blocks are distributed across single rows. (See the next part of Sheet1 on the attachment)

Either way they start out, they need to turn out organized like what you see on Sheet2 on the attachment.

NOTE: Sheet1 has a couple of rows that are blue and are numbered in sequence - left to right. There are 131 available spots on the report, so each of the cells represent an available "spot" on the report. All 131 "spots" load into column A on the report when it's exported into Excel. So, basically, the 131 columns on Sheet1 represent what's in column A when the report downloads. Any cells on Sheet1 that are blank are actually spaces to be dealt with when parsing out the data.
NOTE2: Sheet2 has its top row pink. That just represents the number of the column where the data needs to get moved to, NOT spaces.
(I just did this to help me visualize the spaces, etc.

I know how I'd deal with the problem when the blocks are on different rows, but when there are 2 names (or addresses - whatever) in the same cell, with a LOT of spaces separating them and 2 addresses in the same cell, with a LOT of spaces separating them, etc I just can't picture how to get them to do what I want!

SECOND:The city, state and zip each need to end up in their own cells. If that particular block is alone in the cell, I can do that with Left, Mid, Right. But if there's a City, State, Zip in a cell, then a lot of spaces, then another City, State, Zip - well, I can't seem to picture how to do that. The same problem exists in the "phone number" row.

I'm sorry; I know this is really long, but I want to give you all the info up front.
So far, no epiphanies today, LOL! Maybe you have one?

Thanks!

Jenny

This creates new sheet and output the half processed data from "Raw data-sample" sheet in your post #6.
Then you can do a TextToColumns with a caret ^ and hope make it easier.

Please Login or Register  to view this content.

Originally Posted by jindon

This creates new sheet and output the half processed data from "Raw data-sample" sheet in your post #6.
Then you can do a TextToColumns with a caret ^ and hope make it easier.

Thank you for that coding! However, because the number of spaces between blocks of text varies from row to row, a text-to-columns doesn't seem to be an option. Sometimes, there's only 1 space between pieces of text that actually need to have a delimiter but the macro doesn't assign a delimiter, since there's only 1 space. And sometimes, there are multiple spaces between words that shouldn't have a delimiter but a macro assigns one, since there's more than one space. (The original report was formatted by a crazy person, evidently, LOL!)

Thanks for your time spent on the problem.

Jenny