Prev: How to set cell validation in excel using name range?
Next: How to determine which sheets appear on opening a workbook.
From: Ron Rosenfeld on 22 Mar 2010 12:48
On Mon, 22 Mar 2010 09:26:20 -0400, "Rick Rothstein"
<rick.newsNO.SPAM(a)NO.SPAMverizon.net> 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
From: andreashermle on 22 Mar 2010 13:10
On 22 Mrz., 11:17, Ron Rosenfeld <ronrosenf...(a)nospam.org> wrote:
> On Mon, 22 Mar 2010 02:27:28 -0700 (PDT), andreashermle <andreas.her...(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- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

HI Ron,

great, thank you very much for your professional help. It works as
desired. Regards, Andreas
From: Rick Rothstein on 22 Mar 2010 13:29
>>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)

From: andreashermle on 24 Mar 2010 02:48
On 22 Mrz., 11:23, Mike H <Mi...(a)discussions.microsoft.com> wrote:
> 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
> > .- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

To all of you: Rick, Ron, Mike:

Thank you very much for your terrific support. I really appreciate it.
I am gonna test all the solutions in the coming days and let you know
how everything works.
GREAT SUPPORT.

Regards, Andreas
From: andreashermle on 25 Mar 2010 07:47
On Mar 22, 6:29 pm, "Rick Rothstein"
<rick.newsNO.S...(a)NO.SPAMverizon.net> 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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to set cell validation in excel using name range?
Next: How to determine which sheets appear on opening a workbook.