[Solved] Splitting big cells isn't splitting...

[MrProgrammer]

I am not an expert, but I do know how to use the text to columns function, and I was trying to split a large cell (here's an example of the cell contents):

These natural Rainbow Fluorite beads are:
Quantity: Ten 16" Strands (about 640 beads)
Shape: Smooth round sphere ball shape
Size: 6mm (+/- 0.5mm)
Quality: Fluorite is so difficult to deal with - it's so beautiful, but such a soft stone that it gets beat quite easily. Your AAA grade Fluorite beads can quickly become C grade from traveling around without proper protection. Needless to say - this fluorite is not AAA grade - I'd give it about a B. The B grade comes from the consistent, nice cutting & drilling of the stone, along with its translucence. The grade is lower because the color is not terribly vibrant, but is still lightly varied. If you make malas or other prayer beads, this is a great buy for you!

The stones in the image are representative of the stones you'll receive.


I decided to use the colon for the cell break because the info after the colon is generally what I am looking to separate. What I expected would happen is that I would get a lot of columns with chunks of info in them, but I don't. I get a lot of new rows (what is that about?), only one column, and not every cell is split.

I'm basically looking to pull out the quantity, shape, size, & quality into separate columns in the same row (so they can keep their skus). I can use find & replace to take out the extraneous stuff.

Any ideas on why this isn't working? I've spent the whole day messing with it & can't figure it out!
Thanks for your help!
Liz

[acknak]

Greetings and welcome to the community forum!

I believe your text may have line break characters (aka newlines or line feeds) in it. These will throw off the text-to-columns feature, if I remember correctly.

It would be best if you could attach a sample sheet with a few sample items.

[MrProgrammer]

I get a lot of new rows (what is that about?)

Read [Tutorial] Text to Columns, especially Q31/A31. Text to Columns works fine with line breaks, but one does need to understand how they affect the operation.

I get … only one column, and not every cell is split.

Using a separator of colon worked for me (with OOo 3.2).

You may need to attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

[warriorprincessliz]

Okay,I'm sure you're right about the CR issues, although I don't know how to get them out. I've tried to copy & paste & remove the formatting while doing that, but it didn't seem to work.

Regardless, here is the issue of some cells splitting and others not splitting. I'm attaching two documents. One has the data before I did the text to column split, and the other shows what happens after I do it using a colon as a delimiter. Some of the cells split (with many new rows) & some of them just sit there totally untouched. I am confused by this.

Anyway, here goes...

[warriorprincessliz]

Just an update - I removed the CR in the document using \n & then split the cells using the colon as the delimiter & got everything sorted. BUT, the issue I mentioned above wasn't fixed, so for those of you who are interested for the sake of knowledge - please feel free to try to figure this one - but my particular issue has been solved. Thank you for your help!
Liz

[MrProgrammer]

Regardless, here is the issue of some cells splitting and others not splitting.

This data is quite a mess to work with. Your first cell is:

This◊Moss◊Agate◊bead◊is:¶
Quantity:◊1◊pendant◊bead¶
Shape:◊Flat◊nugget-y◊shape◊(it◊is◊drilled◊lengthwise◊like◊a◊bead)¶
Size:◊34x35mm¶
Weight:◊◊103ct¶
Quality:◊◊This◊bead◊is◊awesome.◊◊…◊to◊a◊generally◊nice◊stone.¶
¶ ← This line has no data
The◊stone◊in◊the◊image◊is◊the◊stone◊you'll◊receive.¶
¶ ← This line has no data
צ
¶ ← This line has no data
◊◊◊¶
◊

Above, ¶ represents a newline character and ◊ represents a blank. (For this post I've shortened the long "This bead is awesome" line.) When you use colon as a delimiter the cells in column A are assigned:

A1 → This◊Moss◊Agate◊bead◊is
A2 → Quantity
A3 → Shape
A4 → Size
A5 → Weight
A6 → Quality
A7 is not changed because the line above has no data
A8 → The◊stone◊in◊the◊image◊is◊the◊stone◊you'll◊receive.
A9 is not changed because the line above has no data
A10 → ◊
A11 is not changed because the line above has no data
A12 → ◊◊◊
A13 → ◊

A7 had a big block of data beginning "This genuine Diamond Bead strand is" but that cell does not get changed by Text to Columns. You might not expect that — I wouldn't either — however that's the way Text to Columns works. The data in that block was processed by Text to Columns and you'll find it in A83:A92. All the cells were split, but some of the original cells remain.

The easiest way to prevent empty lines from causing unexpected (but correct) results is to ensure that there aren't any empty lines. Put formula
=SUBSTITUTE(SUBSTITUTE(A1;UNICHAR(13);UNICHAR(10));UNICHAR(10)&UNICHAR(10);UNICHAR(10)&" "&UNICHAR(10))
in B1 and fill the formula down the column. This changes two adjacent NewLines into Newline Space Newline to ensure that no line is empty. Now run Text to Columns on column B. Cell B1 has value:

This◊Moss◊Agate◊bead◊is:¶
Quantity:◊1◊pendant◊bead¶
Shape:◊Flat◊nugget-y◊shape◊(it◊is◊drilled◊lengthwise◊like◊a◊bead)¶
Size:◊34x35mm¶
Weight:◊◊103ct¶
Quality:◊◊This◊bead◊is◊awesome.◊◊…◊to◊a◊generally◊nice◊stone.¶
◊¶ ← This line has now has data
The◊stone◊in◊the◊image◊is◊the◊stone◊you'll◊receive.¶
◊¶ ← This line has now has data
צ
◊¶ ← This line has now has data
◊◊◊¶
◊

Every cell in column B is now replaced with new data since there are no empty lines.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.