Extracting a number from a multiline-entry

[andreashermle]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Mike H]

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.

 

[Rick Rothstein]

I would like to extract a number from multi-line excel cells. The

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.

 

[Ron Rosenfeld]

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.

I haven't tested that but would that not remove the opportunity to retain
leading zero's?
--ron

 

[andreashermle]

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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

HI Ron,

great, thank you very much for your professional help. It works as
desired. Regards, Andreas

 

[Rick Rothstein]

Or possibly even....

I haven't tested that but would that not remove the opportunity to retain
leading zero's?

Yes, that is true, Val returns a number, not a String, so any formatting
would be lost. However, I would point out that the OP referred to it as a
"number" twice in her original posting, so I went with that. If leading
zeroes were required, then how many digits in the number would more than
likely be known (6 digits for the example the OP gave), so this could be
used if that was the requirement...

Format(Val(Range("A1")), "00000")

 

[andreashermle]

Yes, that is true, Val returns a number, not a String, so any formatting
would be lost. However, I would point out that the OP referred to it as a
"number" twice in her original posting, so I went with that. If leading
zeroes were required, then how many digits in the number would more than
likely be known (6 digits for the example the OP gave), so this could be
used if that was the requirement...

Format(Val(Range("A1")), "00000")

Hi Rick,

thank you very much for your terrific support. The formula for my
problem provided by Ron is the best solution for me. Although your
code snippet is handy for another problem I am having. Thank you very
much. Regards, Andreas

 

[Ron Rosenfeld]

HI Ron,

great, thank you very much for your professional help. It works as
desired. Regards, Andreas

Glad to help.

Thanks for the feedback.
--ron