Text function MID, RIGHT, LEN
 

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

In A1 MY WORLD - AUSTRALIA BIB, 300 CL

In B1
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),COUNT(1*MID(MID(A1,MIN(SEARCH({0,1,2,3,4, 5,6,7,8,9},A1&"0123456789")),99),ROW($1:$9),1)))

If this post helps click Yes
---------------
Jacob Skaria


"tomjoe" wrote:

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$ 255);1)));0);SOMME(--
(ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1))))))
Validate with Shift+Ctrl+Enter

http://cjoint.com/?jvkwRz4kLT

JB
http://boisgontierjacques.free.fr/

On 21 sep, 09:55, tomjoe wrote:
Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?

Tommy

Text function MID, RIGHT, LEN
 

Hi,

A non array solution

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Mike

"tomjoe" wrote:

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

=MID(A1,MATCH(TRUE,ISNUMBER(VALUE(MID(A1,ROW($1:$2 55),1))),0),SUM(--
(ISNUMBER(VALUE(MID(A1,ROW($1:$255),1))))))
Validate with Sfift+Ctrl+enterr

JB

On 21 sep, 10:22, JB wrote:
=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$ 255);1)));0);SOMME(--
(ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1))))))
Validate with Shift+Ctrl+Enter

http://cjoint.com/?jvkwRz4kLT

JBhttp://boisgontierjacques.free.fr/

On 21 sep, 09:55, tomjoe wrote:



Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?

Tommy- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Text function MID, RIGHT, LEN
 

Very much creativity and probably helpful solutions.
I will look into this later today.
Mike: Is it an array fuction I can see within your sumproduct fuction?

Tommy


Mike H skrev:

Hi,

A non array solution

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Mike

"tomjoe" wrote:

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

Hi,

Sumproduct is an array but doesn't have to be array entered. My formula
doesn't use sumproduct and doesn't need to be array entered.

Mike

"tomjoe" wrote:

Very much creativity and probably helpful solutions.
I will look into this later today.
Mike: Is it an array fuction I can see within your sumproduct fuction?

Tommy


Mike H skrev:

Hi,

A non array solution

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Mike

"tomjoe" wrote:

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

On Mon, 21 Sep 2009 00:55:02 -0700, tomjoe
wrote:

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy



If you always want the second to last <space separated string, you could try
this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198)),
FIND(" ",TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198)))-1)

--ron

Text function MID, RIGHT, LEN
 

Does your text strings **always** end with " CL" (there is a space before
the CL) as your examples seem to indicate? If so...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3)," ",REPT(" ",99)),99))

This formula is to be entered normally.

--
Rick (MVP - Excel)


"tomjoe" wrote in message
...
Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

Is there **always** a comma before the number that you want and, if so, is
it **always** the last comma in the text string? If that is the case, and if
my other posting doesn't work for you (if it works, use it before using this
one)...

=LOOKUP(999999,--LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT("
",99)),99)),ROW($1:$99)))

This is also a normally entered formula and it assumes your number will
never be one million or more.

--
Rick (MVP - Excel)


"tomjoe" wrote in message
...
Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

My formula returns the number as a text string... if you need that number to
be a real number, use this modification to my formula instead...

=--TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3)," ",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Does your text strings **always** end with " CL" (there is a space before
the CL) as your examples seem to indicate? If so...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3)," ",REPT(" ",99)),99))

This formula is to be entered normally.

--
Rick (MVP - Excel)


"tomjoe" wrote in message
...
Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right
of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy

Text function MID, RIGHT, LEN
 

Thanks.
OK, so you entered the bracets {0,1,2,3,4,5,6,7,8,9} with the keyboard.
I liked your formula, but somehow I can not get it to work.
In Europe we use other names of the formulas, of course, and also we use
semicolon instead of the comma, because comma is reserved for a decimal
separator. It might be something I missed in the translation.
I will look more into it tomorrow.

Tommy

Mike H skrev:

Hi,

Sumproduct is an array but doesn't have to be array entered. My formula
doesn't use sumproduct and doesn't need to be array entered.

Mike

"tomjoe" wrote:

Very much creativity and probably helpful solutions.
I will look into this later today.
Mike: Is it an array fuction I can see within your sumproduct fuction?

Tommy


Mike H skrev:

Hi,

A non array solution

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Mike

"tomjoe" wrote:

Hi
I try to extract part of a text:
I want to extract the numbers 300, 200 and 5 in the column to the right of
the textstrings under.
MY WORLD - AUSTRALIA BIB, 300 CL
SAVANNAH CHARD FLO-FAIRTRADE BIB, 200 CL
HAMMER LONDON DRY GIN (USA), 5 CL

I have tried:
=MID(B257,FIND(",",B257,1)+1,LEN(B257))
This gives me also the CL (i.e. 300 CL).
Someone who now how I can fix this?


Tommy