replace Blank Spaces in text string by inserting a comma where there are 2 or more spaces.

[blbat]

I have many cells with text strings that may contain 2, or more, blank spaces within that cells text. (I don't care about a blank space of 1). each cell may contain multiple instances of these blank spaces. There is no order to the blank spaces, so i need to find the blank spaces and insert a comma.

My end goal is to do a "text to columns" type of function to separate the individual strings of text.

so a cell A1 that contains:

txt sometext                       text and number-98            20b)1            G 4.1.04.1.0.3            Sep-17              STUFF                  SW 1        Location          GL morestuff                        year2020

would look like:

txt sometext,text and number-98,20b)1,G 4.1.04.1.0.3,Sep-17,STUFF,SW 1,Location,GL morestuff,year2020,

Maybe some combination of LEN and SUBSTITUTE?

 

[Rick Rothstein]

Intriguing macro, Rick. How did you derive the values in the array? Can you show that it works for all sizes of sequential commas (up to some large limit), and even better, if it's an optimal set of numbers?

I did not derive them... I picked them up from an old newsgroup posting (from around 2004) back from when I was a Microsoft MVP for Visual Basic (the compiled version, not the DotNet version) and modified it for non-space characters. Using the 121 top end handles up to 9740 repeated characters... if you add 9841 to the array, it will handle up to 64570080 repeated characters! Anyway, if you are interested in following how those numbers were derived, here is a link to a copy of that old newsgroup thread....

https://groups.google.com/forum/#!topic/microsoft.public.vb.general.discussion/TqZHK9cPnpU

Rick,

I always appreciate it when you pop in on my threads.

that code worked like a charm.

I would very much like to know what you did there...would you mind commenting the lines so I can use it as an educational opportunity?

See above...

 

[Rick Rothstein]

I did not derive them... I picked them up from an old newsgroup posting (from around 2004) back from when I was a Microsoft MVP for Visual Basic (the compiled version, not the DotNet version) and modified it for non-space characters. Using the 121 top end handles up to 9740 repeated characters... if you add 9841 to the array, it will handle up to 64570080 repeated characters! Anyway, if you are interested in following how those numbers were derived, here is a link to a copy of that old newsgroup thread....

https://groups.google.com/forum/#!topic/microsoft.public.vb.general.discussion/TqZHK9cPnpU

By the way, if you choose to read the above newsgroup thread, you can ignore the posts from Bonj... he was an annoying troll who kept bothering us in almost all the vb newsgroup threads back then.

 

[Eric W]

More interesting. I checked out that thread, it appears the values were empirically derived, and exhaustively tested up to the limits mentioned. No claims of being optimal, but there were mentions of some mathematicians looking into it. Something I might noodle around with sometime. I didn't find mention of that sequence in the OEIS, either using the values themselves or some key search terms, so it looks like there's an opportunity there for someone.

(And yeah, I learned to skip over Bonj quite quickly!)