Using a formula instead of "Text to Column" in a shared workbook

[tav1035]

I have a workbook with over 50,000 rows of data. My "N" column has some data delimited by commas. I need to parse the first 3 sections of the text in the "N" column into 3 separate columns (K, L, M). Can't have trailing spaces or commas.
I can't use "text to column" due to this is a shared workbook and I can't unshare it. Also each formula result will have to be analyzed to ensure it is what we want to use. We may not like the results and we may need to hand type a result into a row without using the formula on a few of them. Formulas would give us that flexibility.

Here is some examples of my data.

http://www.datapaste.com/postexcel/Book1.xls

Thanks,
tav

 

[tav1035]

Ok i think I found a pretty good solution that I have never seen before.

AIR CONDITIONING UNIT, SPLIT, COMPUTER ROOM UNIT


AIR CONDITIONING UNIT
=TRIM(MID(SUBSTITUTE("," & $N1&REPT(",",6),",",REPT(" ",255)),1*255,255))


SPLIT
=TRIM(MID(SUBSTITUTE("," & $N1&REPT(",",6),",",REPT(" ",255)),2*255,255))


COMPUTER ROOM UNIT
=TRIM(MID(SUBSTITUTE("," & $N1&REPT(",",6),",",REPT(" ",255)),3*255,255))

For the 3rd word change from 2*255 to 3*255

referenced from

http://www.excelbanter.com/showthread.php?t=259338

The one thing it doesn't account for is if there are two commas in a row.
ie..
AIR CONDITIONING UNIT,, SPLIT, COMPUTER ROOM UNIT
This will mess the placement up and the word SPLIT becomes the third word.

Maybe someone has a solution to the double commas?


Thanks,
tav

 

[BobJacksonNCI]

NICE!

I saw that no one had posted, so I was playing with it.
Had come to the conclusion that you might have to take the workbook offline to make the change.

Regarding the possibility of double commas, you could just run a SUBSTITUTE first. Something like the following in another column:

=SUBSTITUTE(N1,",,",",")

Then copy/paste the updated column on top of the original.
(You DO have it backed up ... !!! ???)

Good Luck!


HTH,
Bob