Extracting a number from a multiline-entry
in [Programming]
|
Prev: How to set cell validation in excel using name range?
Next: How to determine which sheets appear on opening a workbook.
From: andreashermle on 22 Mar 2010 05:27 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
From: Ron Rosenfeld on 22 Mar 2010 06:17 On Mon, 22 Mar 2010 02:27:28 -0700 (PDT), andreashermle <andreas.hermle(a)gmx.de> 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 If the format is exactly as you post, with ONLY the number on the line prior to the first manual line-break, then: =LEFT(A1,FIND(CHAR(10),A1)-1) will return that string of numbers as a text string, or: --=LEFT(A1,FIND(CHAR(10),A1)-1) to return it as a number. Note that the manual line break character is ASCII code 10. --ron
From: Ron Rosenfeld on 22 Mar 2010 06:22 On Mon, 22 Mar 2010 02:27:28 -0700 (PDT), andreashermle <andreas.hermle(a)gmx.de> 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 And since this question is in a programming group, the equivalent VBA expression could be: Left(Range("a1"), InStr(Range("a1"), Chr(10)) - 1) or even: Split(Range("a1"), Chr(10))(0) --ron
From: Mike H on 22 Mar 2010 06:23 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 > . >
From: Rick Rothstein on 22 Mar 2010 09:26
>>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 > > > And since this question is in a programming group, the equivalent VBA > expression could be: > > > Left(Range("a1"), InStr(Range("a1"), Chr(10)) - 1) > > or even: > > Split(Range("a1"), Chr(10))(0) Or possibly even.... Val(Range("A1")) as long as the number is either a whole number or a floating point number that uses a dot for its decimal point. -- Rick (MVP - Excel) |