Text function MID, RIGHT, LEN

  • Thread starter Thread starter tomjoe
  • Start date Start date
T

tomjoe

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
 
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 B
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),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
 
Hi,

A non array solution

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

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

JB
 
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,

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

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
 
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.
 
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.
 
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))
 
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:
 
Back
Top