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?

 

[sandy666]

maybe Find and Replace:

Find what: " " (two spaces)
Replace with: "," (single comma)

then again:
Find what: ",,"
Replace with: ","

no quotes

 

[blbat]

If I use "find and replace" from tool bar I end up with Multiple commas:

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

that's why I'm looking for a way to get a single comma.

 

[sandy666]

did you read my whole post ?

second step try few times to see answer: We couldn't find anything to replace... etc bla bla

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

 

[blbat]

I get what you're saying, but then I have to find and replace for anywere from 2 to 45, or more, commas...it seems a little unwieldy. I do appreciate the input though!

 

[sandy666]

you can select whole range with text like you show above then do Find and Replace

of course you can use formula or vba but it will be exactly the same with another way

good luck

 

[blbat]

=SUBSTITUTE(A1, "  ", ",", 1)

inserts a comma in the FIRST instance of a blank space with 2 or more spaces, so i get this:

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

I just need to figure out how to get it to replace every instance of the 2 or More Blank Spaces in that cell....

 

[Rick Rothstein]

Give this macro a try...

Sub MultipleSpacesToSingleComma()
  Dim V As Variant
  Columns("A").Replace Space(2), ",", xlPart, , , , False, False
  Columns("A").Replace ", ", ","
  For Each V In Array(121, 13, 5, 3, 3, 2)
    Columns("A").Replace String(V, ","), ","
  Next
End Sub

 

[Eric W]

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?

 

[blbat]

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?


I was trying real hard to do this with a plain formula, with various permutations like these:

=IF(LEN(A1)-LEN(TRIM(A1))>0,SUBSTITUTE(A1, "  ", ",","NO SPACE!"))


 =IF(MID(SUBSTITUTE(A2, "  ", ",", 1))

which, of course, do NOT work.

is this even possible with a plain formula?



Regards,

Blbat