There several line feeds in a cell. If the first line feed is in position 2 then result will be left 1 if not then left 3
This does not work:-
=IF(CODE(MID(PDF.L4,2,1),10)),LEFT(PDF.L4,1),LEFT(PDF.L4,3))
Any help please?
- “doesn’t work” Isn’t a useful information. Always tell what you actually get. (Empty? Error message? 1232545.78$ ?
- Your formula has no reasonable syntax. If you want to compare a CODE() to a value (10 in this case), the comma isn’t the appropriate operator.
- Make it as simple as possible. Neither the sheet name nor the specific cell are of a relevant meaning for the issue here.
(Solutions disregarding the sheet name:)
=REGEX(A1;"^.(?=\n)";;1)
correction: =IFNA(REGEX(A1;"^.(?=\n)";;1);LEFT(A1;3)) or similar
=IF(CODE(MID(A1;2;1))=10;LEFT(A1;1);LEFT(A1;3))
Be aware of the fact that LEFT(A1;3) may contain a non-printable character, in specific an LF. You will only see two or less characters then.
You may also consider to regard what I wrote in my answer here: Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? .
Agree I should have show “Err.508” Using version 7.1.8.1
Solution =REGEX(PDF.L4,"^.(?=\n)",1) works. I am on another sheet; not PDF sheet PDF.
Thank you for your help.
I supposed so. It’s irrelevant, however, for the working of the formula.
More important is that the first formula I suggested, and which you accepted, didn’t regard your second case. If there isn’t a LineFeed in the second place of the string, that formula will return an #N/A error.
Sorry. To get what you want also in the second case, you may use the IFNA() function in the following ay (or similar):
=IFNA(REGEX(Sheet1.A2;"^.(?=\n)";;1);LEFT(Sheet1.A2;3)).
See also the attached example:
disask73947SpecialConditrionalPartOfString.ods (12.5 KB)
(My remarks concerning the comma as the argument separator weren’t just a bad joke.)
You may also accept the solution formally by clicking the respective checkmark. It’s good practice.