Multi Line Cell Spliting

Please find attached file, i cannot post the actual file as it contains personal data but it is in the same layout.

Thanks
Sam

I believe your delimiter is char(10).

Originally Posted by bentleybob

I believe your delimiter is char(10).

I have tried giving a blank cell '=CHAR(10)' and copying it into the 'other' delimited field to no avail. I believe that CHAR(10) is the same as Alt+010 and Ctrl+J which as stated originally i have tried also.

Thanks
Sam

Have noticed that in my sample attachment the text to column seems to work, but it does not work in my actual file?! Not sure why there does not appear to be any difference!

Thanks
Sam

Try the attached. Formulas get a little clunky, but it does work.

Originally Posted by bentleybob

Try the attached. Formulas get a little clunky, but it does work.

When copied over, using same cell positions etc, what seems to work correctly in sample, does not work in real file, the first 3 formulas work but the last 2 do not they get the #value! error. It always seems to be the county... which i don't know why that makes a difference as the counties are different.

I understand it is hard with out being able to see the actual file and 'play' with it so i appreciate your patience.

Many Thanks
Sam

Originally Posted by martindwilson

maybe your original isnt char(10)
test one of them by deleting only the first line of text leaving the space after it intact
them use =code(c2)
(fwiw i get value error as well in col m,n)

Seems to be CHAR(10) and thanks for confirming the error on column m, n. Its not me not going mad!

Thanks
Sam

I got the same errors when there was missing data (like the country). Try ding a search and replce -- in each of the formulas in row 2 and then copying down -- substituting $c2&char(10) for $c2

If needed, do the search and replace multiple times (it just adds the CR at the end of the reference to your text, so adding more CRs doesn't affect anything).

Please see the attached for clarification.

As Martin suggests, if you're not missing data, then the delimiter for the country might somehow be different from the others.

Hi,

Thanks for all the assistance with this btw!

The short formulas work perfectly, but im still having the issue with the counties, im sure you are right and it must have a different delimiter some how, even though it looks excactly the same?!

I'll have to go over it all with a fine tooth comb see if i can come up with anything!

Thanks again
Sam

p.s ill let you know if i find the issue!!

As another thought

The county is always the last line in the cell.
The county always recieves an error when the cell contains 3 or 4 lines.
The county always shows and the formulas always work perfectly when the cell contains 5 lines.

So... is this because the formulas given only work when the cell contains 5 lines and some how 'break' when the cell contains less than 5 lines???

Please excuse my ignorance as i cannot make head nor tails of the formulas you guys have used they are currently beyond me, thank god for copy&paste!!

Thanks
Sam

That's why I suggested adding &code(10) at least once to each reference to the original cell (using search and replace). That will get rid of the error messages; you will see blanks instead.

Originally Posted by bentleybob

That's why I suggested adding &code(10) at least once to each reference to the original cell (using search and replace). That will get rid of the error messages; you will see blanks instead.

I'm sorry i am baffled as to which cells i am suppose to use search and replace on? and what im supposed to be searching for and replacing

I'm going to put it down to a long day!

If it helps i have duplicated the error on the sample attached, as you can see if it have 3 lines the 3rd column produces an error instead of showing the 3rd line, same for the 4th line...etc

If you could point me in the right direction, i would appriciate it

Thanks
Sam

NEVERMIND BENTLEYBOB!

I have understood what you meant and now it works perfectly!

THANK YOU VERY MUCH!!!!!

Sam