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]

 

[Peter_SSs]

Okay, here is my corrected "corrected" code ...

I'll stick with the post #7 code as on my test data it was about twice as fast.

 

[István Hirsch]

Aren't you interested in a formula solution? If you are, enter this in B1 and copy across and down as needed:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE("."&$A1," ","ß"),".", REPT(" ",300)),COLUMNS($A:A)*300,300),"ß"," "))

 

[hiker95]

Manolocs,

How about something like this?

Excel 2007
	A	B	C	D	E
1	help mary. help john. help Louis	help mary	help john	help Louis
2	Johny is walking. Andrew is sleeping	Johny is walking	Andrew is sleeping
3
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(". "&$A1,". ",REPT(" ",300)),COLUMNS($A:A)*300,300))
C1	=TRIM(MID(SUBSTITUTE(". "&$A1,". ",REPT(" ",300)),COLUMNS($A:B)*300,300))
D1	=TRIM(MID(SUBSTITUTE(". "&$A1,". ",REPT(" ",300)),COLUMNS($A:C)*300,300))
E1	=TRIM(MID(SUBSTITUTE(". "&$A1,". ",REPT(" ",300)),COLUMNS($A:D)*300,300))

The formula in cell B1, copied down, and, to the right, as needed:

=TRIM(MID(SUBSTITUTE(". "&$A1,". ",REPT(" ",300)),COLUMNS($A:A)*300,300))

 

[Rick Rothstein]

Aren't you interested in a formula solution? If you are, enter this in B1 and copy across and down as needed:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE("."&$A1," ","ß"),".", REPT(" ",300)),COLUMNS($A:A)*300,300),"ß"," "))

Manolocs,

The formula in cell B1, copied down, and, to the right, as needed:

=TRIM(MID(SUBSTITUTE(". "&$A1,". ",REPT(" ",300)),COLUMNS($A:A)*300,300))

What about if the text in the cell was this?

Mr. Jones is walking.      Mrs. Johnson is sleeping

 

[István Hirsch]

Thanks, Rick.

I had thought the strings were to be separated by the full stops.

This formula is to separate the strings at places where there are more than 4 consecutive spaces:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE("ß"&$A1," ","ß"))," ","#"),"ß"," "))," ",REPT(" ",LEN($A1))),COLUMNS($A:A)*LEN($A1),LEN($A1)),"#"," "))

Important note: the editor reduced the number of spaces to one. There should be FIVE spaces between the red quotes.

 

[Peter_SSs]

This formula is to separate the strings at places where there are more than 4 consecutive spaces:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE("ß"&$A1," ","ß"))," ","#"),"ß"," "))," ",REPT(" ",LEN($A1))),COLUMNS($A:A)*LEN($A1),LEN($A1)),"#"," "))

Important note: the editor reduced the number of spaces to one. There should be FIVE spaces between the red quotes.

Istvan, a couple of comments to consider, though I don't really know what the actual data may be like.

1. You may want to consider a modification in case the data cell contains no occurrences of 5 or more spaces. eg "abc" or "ab cd". At the moment it incorrectly splits that text.

2. Assuming this formula goes into column B initially, I would consider changing COLUMNS($A:A) to COLUMNS($B:B). That way it is more robust if a new column is subsequently inserted between A and B.

 

[István Hirsch]

Thanks, Peter.

This formula is to separate the string at places where there are more than one space:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE("ß"&$A1," ","ß"))," ","#"),"ß"," "))," ",REPT(" ",2000)),COLUMNS($B:B)*2000,2000),"#"," "))

Imortant note: there should be TWO spaces entered between the red quotes.