Hi,

Like this

=LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

or if there could be a leading zero you want to retain the slightly longer

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"andreashermle" wrote:

Dear Experts:

I would like to extract a number from multi-line excel cells. The
cells have the following make-up


123456(manual line break = Alt+Enter)
sample text (manual line break = Alt+Enter)
sample text

In the above example I would like to extract the number located
immediately before the first manual line break and display it in a
separate cell. Can this be done using a formula/function?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
.