Using Text to Columns with spaces as the delimiter

[sammon28]

I'm trying to use Text to Columns to break up a row of Text that basically looks like "XX xxxxxxx xxx xx xxxxxxx xxx" - the only thing that I can use as the delimiter is the fact that there are 3 spaces in between the pieces of text I want to break up. But the Text to columns feature doesn't let you use multiple spaces as the way to split up the text. Anyone have any ideas?

Thanks...

 

[crook_101]

Hi,

Is the text regular in every cell you want to split, like serial numbers etc??

If you could post some examples that would assist with a solution


Cheers,
Ian

 

[Akashwani]

Hi,

Welcome to MrExcel.

This probably isn't the best method, but it works....

Excel Workbook
	A	B
1	Original Data	Using TRIM
2	XX xxxxxxx xxx xx xxxxxxx xxx	XX xxxxxxx xxx xx xxxxxxx xxx
Sheet3

Excel Workbook
	A	B	C	D	E	F	G
1	Original Data	After Text to Columns
2	XX xxxxxxx xxx xx xxxxxxx xxx	XX	xxxxxxx	xxx	xx	xxxxxxx	xxx
Sheet3

As you can see I have used TRIM to reduce the spaces within the text.
I then did copy - paste special Values, then I did the Text to Columns.

Is this what you need?

Ak

 

[crook_101]

Hi again,


Using the above as inspiration:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">abced   12345</td><td style=";">abced</td><td style="text-align: right;;">12345</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">" ",A1</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,FIND(<font color="Red">" ",A1</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />

HTH
Ian

 

[Haseeb Avarakkan]

B1, copy across & down.

=TRIM(MID(SUBSTITUTE(" "&TRIM($A1)," ",REPT(" ",255)),COLUMNS($B1:B1)*255,255))

 

[BrooksTech]

Using Text to Columns Fastest way to Delimit

First, I want to say all of the previous suggestions are terrific. But for the sake of covering all bases and methods, heres another. Its only if youre doing Text To Columns ONE TIME and dont plan on adding any additional data:

Utilizing the EDIT>>REPLACE function, simply put a space in the FIND field, then say a comma or semicolon in the REPLACE field. Then Select REPLACE ALL. Beware it will replace ALL blanks in EVERY column with the substituted character unless you choose one at a time (FIND NEXT). Another alternative is if the data in the column is unique such as "2009 123456 ..." then you can FIND "2009 " and REPLACE with "2009,"

This is of course the most rudimentary approach but its fast, easy, and you dont have to worry about formulas and syntax errors, especially on a ONE TIME conversion. Good Luck.

 

[sammon28]

CAT FOOD CAT FOOD (there are three spaces between the first "food" and the 2nd word "cat" even though it doesn't show it on here)

OK, if the above is in Cell A1, I need to be able to use some formula to end up with the following:

Cell B1: CAT FOOD
Cell C1: CAT FOOD

I also can't just have it put one of the "CAT FOOD" items in B1 then copy. Occasionally there are discrepancies in what will be in A1, such as:

CAT FOOD DOG FOOD

I literally need the first set of words before the three spaces to be in B1, and then the 2nd set of words after the three spaces to be in column C1 so I can do a comparison. I need it to break the cell in half based on what comes before the three spaces, and what comes after.

 

[Peter_SSs]

Try this. Note that there are 3 spaces between the "" in the FIND function.

Excel Workbook
	A	B	C
1	CAT FOOD DOG FOOD	CAT FOOD	DOG FOOD
2	CAT FOOD CAT FOOD	CAT FOOD	CAT FOOD
Split Cell

 

[Peter_SSs]

Akashwani

It turns out the result you were producing wasn't what the OP wanted, but if it was it could be achieved with Text to Columns directly by using a space as the delimiter and checking the 'Treat consecutive delimiters as one' box at step 2 of the wizard. So no need to Trim/Copy/Paste first.


crook_101

Note that your suggestion in post #4 split the cell at the spaces but that was only a coincidence. Test your formulas with "ab 12345" in cell A1.