Replace multiple spaces with comma using formulas

[YasserKhalil]

Hello everyone
Suppose I have a string like that

Hello  Chandoo Members   How are you

There are two spaces after 'Hello' and three spaces after 'Members'
I need to replace those multiple spaces with comma " , "
But as for one space to skip ..

The desired output would be

Hello , Chandoo Members  , How are you

 

[shrivallabha]

You can use SUBSTITUTE like below:
=SUBSTITUTE(A1,"__","_,_")
Please note that the board software replaces consecutive spaces. So replace red marked underscores with spaces.

 

[NARAYANK991]

Hi ,

Try this :

Public Function ConvertSpacesToComma(inputrange As Range)
                Const COMMA = " , "
                Dim Length As Integer, i As Integer
                Dim inputtext As String
               
                inputtext = inputrange.Value
                Length = Len(inputrange)
                i = 1
                Do While i <= Length
                    currchar = Mid(inputtext, i, 1)
                    If currchar = " " Then
                      If Not outsideword Then startofspaces = i
                      outsideword = True
                      spacecounter = spacecounter + 1
                    End If
                    i = i + 1
                   
                    If currchar <> " " Then
                      If outsideword And spacecounter > 1 Then
                          inputtext = Application.WorksheetFunction.Replace(inputtext, startofspaces, spacecounter, COMMA)
                          If spacecounter = 2 Then
                            Length = Length + 1
                            i = i + 1
                          Else
                            Length = Length - (spacecounter - 3)
                            i = i - (spacecounter - 3)
                          End If
                      End If
                      outsideword = False
                      spacecounter = 0
                    End If
                Loop
               
                ConvertSpacesToComma = inputtext
End Function

Narayan

 

[YasserKhalil]

Thank you very much for replies and solutions
As for substitute I know it but I don't know how to deal with spaces more than one space ...
As for using UDF I appreciate your help but I already have a UDF for that purpose

Function ReplaceSpaces(param As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\s{2,}"
    If .Test(param) Then ReplaceSpaces = .Replace(param, " , ")
  End With
End Function

and it is working well too

I need to do this task using a formula

 

[Artik]

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"  ","^"),"^ "," ^"),"^"," , ")

Artik

 

[YasserKhalil]

Thank you very much
It is working for the example provided .. where there are two spaces or three .. But I need to deal with any number of spaces
Try this

Hello                            Chandoo Members          How are you

 

[shrivallabha]

As of now I can think of below kind of approach if you insist on formula (but it will falter with bigger spaces if you have) until someone comes with more elegant approach. As indicated before replace 2 underscores with actual spaces. You can extend the substitution levels to suit as it is just simple nesting.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"__"," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")

Edit: TRIM is not required.

 

[YasserKhalil]

Thanks a lot but it doesn't work for me .. it seems that I have to reply on the UDF in that case
Thank you very much for great help

 

[shrivallabha]

Just curious, can you post 200 rows of real data?

 

[YasserKhalil]

The real data is in Arabic and the essence is the number of spaces .. I gave you an example and the rest of data is similar to that pattern
The UDF deals perfectly with the pattern

 

[shrivallabha]

I made a mistake while testing previous formula as TRIM is indeed required. I was curious to know as to what is the maximum number of consecutive spaces you have in real time scenario.

Refer attached sheet. The nested SUBSTITUTE formula will handle up to 125 spaces. Nesting can be increased.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"__"," , "))," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")," , , "," , ")

 

[YasserKhalil]

That's great and awesome Shrivallabha
Thank you very much for this working formula .. I tested it and it gives me charming results
Thanks a lot

 

[shrivallabha]

Thanks for the feedback! That makes it worthwhile to try and solve different issues.

 

[NARAYANK991]

Hi Shrivallabha ,

See if this works equally well :

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", "|"), "||", "|"),"||", "|"), "||", "|"),"||", "|"), "||", "|"), "||", "|"),"|", " , "))

Narayan

 

[shrivallabha]

Hi Shrivallabha ,

See if this works equally well :

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", "|"), "||", "|"),"||", "|"), "||", "|"),"||", "|"), "||", "|"), "||", "|"),"|", " , "))

Narayan

Narayan, yes it works well!