Split cells text between more than 4 spaces

[Manolocs]

Hi, I know Text to Column option can be used to split text but split every word with a single space to different cell.
What I am looking for is a way to split a cell every time there are more than one space between the words.
In this case the strings are separated by more than 4 spaces


[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]help mary. help john. help Louis[/TD]
[TD]help mary[/TD]
[TD]help john[/TD]
[TD]help Louis[/TD]
[/TR]
[TR]
[TD]Johny is walking. Andrew is sleeping[/TD]
[TD]Johny is walking[/TD]
[TD]Andrew is sleeping[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Macropod]

The strings in your example all have a period (.) at the end, so you could use that as the delimiter (as you already appear to have done). If you want to retain the periods, you could use Find/Replace, where:
Find = .
Replace = .,
then use the comma as the delimiter.

Note: It's not apparent from your post that there are four consecutive spaces anywhere in the data. If there are, you could use Find/Replace to replace all four of those with a single comma, then use the comma as the delimiter.

 

[Manolocs]

Sorry for some reason the spaces changes to period.... there are some spaces in between

 

[Macropod]

In that case, you could use Find/Replace to replace all four of those with a single comma, then use the comma as the Text-to-Columns delimiter.

 

[Rick Rothstein]

Hi, I know Text to Column option can be used to split text but split every word with a single space to different cell.
What I am looking for is a way to split a cell every time there are more than one space between the words.
In this case the strings are separated by more than 4 spaces


[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]help mary. help john. help Louis[/TD]
[TD]help mary[/TD]
[TD]help john[/TD]
[TD]help Louis[/TD]
[/TR]
[TR]
[TD]Johny is walking. Andrew is sleeping[/TD]
[TD]Johny is walking[/TD]
[TD]Andrew is sleeping[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can you make use of a macro solution? If so (change the StartRow and Col variable to match your actual layout)...

[table="width: 500"]
[tr]
	[td]Sub SplitOnMultiSpaces()
  Dim R As Long, StartRow As Long, Col As String, vNum As Variant, Data As Variant
  StartRow = 1
  Col = "A"
  Data = Range(Cells(StartRow, Col), Cells(Rows.Count, Col).End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Data(R, 1), Space(2), Chr(1))
    For Each vNum In Array(13, 5, 3, 3, 2)
      Data(R, 1) = Replace(Data(R, 1), Chr(1) & Chr(1), Chr(1))
    Next
  Next
  With Cells(StartRow, Col).Offset(, 1).Resize(UBound(Data))
    .Cells = Data
    .TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
  End With
End Sub[/td]
[/tr]
[/table]

 

[Manolocs]

Hi Rick Rothstein,
You are always there to help someone.... works perfect thanks

 

[Peter_SSs]

Comments:
- I can't see what vNum or Array(13, 5, 3, 3, 2) has to do with the solution.
- If any of the data is separated by an odd number of spaces (some of the sample given was) then the code leaves some of the result data with a leading space.
- It may not be possible with the actual data, but if any of it is separated by a lot of spaces (>65?) then the code will leave a blank column in the result for that row.

Another suggestion that addresses the above points:

[color=darkblue]Sub[/color] SplitOnMultiSpaces2()
  [color=darkblue]Dim[/color] R [color=darkblue]As[/color] [color=darkblue]Long[/color], Data [color=darkblue]As[/color] [color=darkblue]Variant[/color]
  
  [color=darkblue]Const[/color] StartRow [color=darkblue]As[/color] [color=darkblue]Long[/color] = 1
  [color=darkblue]Const[/color] Col [color=darkblue]As[/color] [color=darkblue]String[/color] = "A"
  
  Data = Range(Cells(StartRow, Col), Cells(Rows.Count, Col).End(xlUp))
  [color=darkblue]With[/color] CreateObject("VBScript.RegExp")
    .Global = [color=darkblue]True[/color]
    .Pattern = " {2,}"
    [color=darkblue]For[/color] R = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data)
      Data(R, 1) = .Replace(Data(R, 1), Chr(1))
    [color=darkblue]Next[/color] R
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  Application.ScreenUpdating = [color=darkblue]False[/color]
  [color=darkblue]With[/color] Cells(StartRow, Col).Offset(, 1).Resize(UBound(Data))
    .Value = Data
    .TextToColumns , xlDelimited, , , [color=darkblue]False[/color], [color=darkblue]False[/color], False, False, [color=darkblue]True[/color], Chr(1)
    .CurrentRegion.Offset(, 1).Columns.AutoFit
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Rick Rothstein]

Comments:
- I can't see what vNum or Array(13, 5, 3, 3, 2) has to do with the solution.

It is a method to reduce multiple consecutive delimiters (one or more characters) down to a single delimiter which I used to reduce the Chr(1) delimiter that I introduced to replace double spaces in the cell text. It is from an old, old, old newsgroup thread (which I was not involved with directly) back from when I was volunteering answering questions for the compiled version of Visual Basic. Here is a link to a copy of that thread in case you want to see how those numbers came to be...

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

Comments:
- If any of the data is separated by an odd number of spaces (some of the sample given was) then the code leaves some of the result data with a leading space.

That is because I forgot about the space that separates the sentences, one of which will be left over if there are an odd number of spaces separating them. In the fixed code below, the red highlighted Replace function call fixes that problem.

Comments:
- It may not be possible with the actual data, but if any of it is separated by a lot of spaces (>65?) then the code will leave a blank column in the result for that row.

I made a judgment call based on the posted examples that there would not be anywhere near that many spaces, but since it only involves one more iteration of the loop you commented about above, I have added 121 (highlighted in green in the fixed code below) to the numbers being looped which will eliminating creating the extra column you mentioned unless the text has 4921 or more consecutive spaces in it.



Corrected code (for that which I posted in Message #5)

Sub SplitOnMultiSpaces()
  Dim R As Long, StartRow As Long, Col As String, vNum As Variant, Data As Variant
  StartRow = 1
  Col = "A"
  Data = Range(Cells(StartRow, Col), Cells(Rows.Count, Col).End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = [B][COLOR="#FF0000"]Replace([/COLOR][/B]Replace(Data(R, 1), Space(2), Chr(1))[B][COLOR="#FF0000"], Chr(1) & " ", "")[/COLOR][/B]
    For Each vNum In Array([B][COLOR="#008000"]121,[/COLOR][/B] 13, 5, 3, 3, 2)
      Data(R, 1) = Replace(Data(R, 1), Chr(1) & Chr(1), Chr(1))
    Next
  Next
  With Cells(StartRow, Col).Offset(, 1).Resize(UBound(Data))
    .Cells = Data
    .TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
  End With
End Sub

 

[Peter_SSs]

It is a method to reduce multiple consecutive delimiters...

In relation to my first comment, my point was that you didn't use those vNum values in your code.

No matter what values you put in the array, your code is simply halving the number of Chr(1) in the text each time through the loop. Adding the 121 simply halves one more time.

Instead of adding the 121, I think using vNum in its loop would suffice.

For Each vNum In Array(13, 5, 3, 3, 2)
  Data(R, 1) = Replace(Data(R, 1), String(vNum, Chr(1)), Chr(1))
Next

 

[Rick Rothstein]

In relation to my first comment, my point was that you didn't use those vNum values in your code.

OMG, you are right... what was I thinking ... that double delimiter was supposed to have been a String function call (that is how I have it in my notes) as you point out below. Thanks for catching that!

Instead of adding the 121, I think using vNum in its loop would suffice.

For Each vNum In Array(13, 5, 3, 3, 2)
  Data(R, 1) = Replace(Data(R, 1), String(vNum, Chr(1)), Chr(1))
Next

Yes, doing it that way (now that I calmly think about it) would be guaranteed to handle 120 consecutive Chr(1) delimiter characters (each of which is equivalent to 2 spaces), adding the 121 would boost that guarantee to about 19,680 or so consecutive spaces. Actually, without the 121, the Array will work for most, but not all, consecutive delimiters in excess of 120, hence the word "guaranteed".


Okay, here is my corrected "corrected" code (I left the 121 in on purpose figuring it cannot hurt)...

Sub SplitOnMultiSpaces()
  Dim R As Long, StartRow As Long, Col As String, vNum As Variant, Data As Variant
  StartRow = 1
  Col = "A"
  Data = Range(Cells(StartRow, Col), Cells(Rows.Count, Col).End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Replace(Data(R, 1), Space(2), Chr(1)), Chr(1) & " ", "")
    For Each vNum In Array(121, 13, 5, 3, 3, 2)
      Data(R, 1) = Replace(Data(R, 1), String(vNum, Chr(1)), Chr(1))
    Next
  Next
  With Cells(StartRow, Col).Offset(, 1).Resize(UBound(Data))
    .Cells = Data
    .TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
  End With
End Sub