Splitting Data from Cells

**Abgirl** · 07-12-2010, 06:52 AM

Hi All,
please be gentle!
I've got a column of data that I need to split out - annoyingly some of the cells have multiple entries & not all of them have usefully placed delimiters (,:; etc)
Weirdly (and annoyingly!) when I try to use 'text to columns' on a cell with clear delimiters, I seem to lose some of the data - not sure if this is because there are 'carriage returns' in the cells - I have used the trim function and I still seem to lose info - does trim not cope with carriage returns in cells?
hmm - this may be more than one problem!
any help gratefully received!!

**arthurbr** · 07-12-2010, 06:55 AM

Hi and welcome to the board

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that
should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Abgirl** · 07-12-2010, 07:06 AM

Hi - thanks for that - have attached an example of the type of data I have!

**arthurbr** · 07-12-2010, 07:14 AM

And how do you want to split the data ? Pse post an example of the outcome needed

To clean your data you can use following formula in col b =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) and drag down as needed

**Abgirl** · 07-12-2010, 07:20 AM

Hi - sorry I need each 'system' reference in a separate row if thats possible?!

**arthurbr** · 07-12-2010, 07:25 AM

Do you mean every string starting with the word " system" ? What about h(h) ?

**Abgirl** · 07-12-2010, 07:33 AM

yes, each system name on a separate line - you'll see that some have () and some dont - this is what is making it really tricky! so for instance I need
system a (sysa)
system b (sysb)
system c
system d (sysd)
system e

etc each in cell 1 of each row

Hope that makes sense?