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

On Mon, 22 Mar 2010 02:27:28 -0700 (PDT), 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

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

On 22 Mrz., 11:17, Ron Rosenfeld wrote:
On Mon, 22 Mar 2010 02:27:28 -0700 (PDT), 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

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

On Mon, 22 Mar 2010 10:10:57 -0700 (PDT), andreashermle
wrote:

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

On Mon, 22 Mar 2010 02:27:28 -0700 (PDT), 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


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

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)

On Mon, 22 Mar 2010 09:26:20 -0400, "Rick Rothstein"
wrote:

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

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?

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")

--
Rick (MVP - Excel)

On Mar 22, 6:29*pm, "Rick Rothstein"
wrote:
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?

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")

--
Rick (MVP - Excel)

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

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
.