Extracting a number from a multiline-entry

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
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
Click to expand...

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
 
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
Click to expand...


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
 
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.
 
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)
Click to expand...

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.
 
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.
Click to expand...

I haven't tested that but would that not remove the opportunity to retain
leading zero's?
--ron
 
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 -
Click to expand...

HI Ron,

great, thank you very much for your professional help. It works as
desired. Regards, Andreas
 
Or possibly even....
I haven't tested that but would that not remove the opportunity to retain
leading zero's?
Click to expand...

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")
 
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")
Click to expand...

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
 
Back
Top