Page 1 of 1
Using search or find function for line breaks
Posted: Tue Nov 17, 2009 8:05 pm
by andy713
How can I use either the search or find function in a formula to locate the position of a line break in another cell? In Excel this is char(10). I've tried this and other characters and I enabled regex and tried \n so far. I hope this is possible.
Thanks,
Andy Johnson
Re: Using search or find function for line breaks
Posted: Thu Nov 19, 2009 2:23 am
by mriosv
Using
=SEARCH(CHAR(10);F22;1)
works well to find line break (Ctrl-Enter).
OOo 3.1.1
Re: Using search or find function for line breaks
Posted: Tue Nov 24, 2009 4:44 am
by andy713
I'm sorry, but that just doesn't work. If it was that easy it would have worked when Calc opened the Excel spreadsheet. It returns #VALUE! Just to be sure it wasn't because it was sourced from Excel, I tried it in a new empty Calc worksheet, and it's the same error: abc (cntl-enter) ef gh (cntl-enter) ijk in cell A1, =search(char(10);a1;1) in b1 displays #VALUE!. This happens in both Linux and Windows versions. As an additional test, copy the string in a1 down through multiple cells, and enter the formula =CODE(MID(A1;ROW();1)) in b1 and copy it down too. The code for (cntl-enter) is 32, the same as a space. Since this is obviously wrong, there must be some other trick. Or it is something that Calc is just not capable of doing.
Andy