Different way to retrieve numbers a text string.

[jonagpa]

Hello guys!

So I have a problem, I have a text string and want to learn how to retrieve numbers from it based on my need.

Here is an example of the text string I am working against "5 of 10, missing 5", it could also be "99999 of 99999, missing 99999"(Don't focus on the math)
Always in this format (number1)(space)("of")(space)(number2)(coma)(space)("missing")(space)(number3).
Numbers could be 1 digit up to 5 digits each.

I want to learn how to retrieve the number1, number2, number3 on their own. Any advice?
I would prefer to know the most efficient way to do it, so I can keep the load low on the PC.

[Jonmo1]

With the string in A1

number1: =LEFT(A1,FIND(" ",A1))+0
number2: =MID(A1,FIND(" ",A1)+4,FIND(",",A1)-FIND(" ",A1)-4)+0
number3: =RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))+0

[jonagpa]

This works great! Im not sure if i need to start a new thread, moderators please let me know if I should. I would like to expand on this a bit. I have a range lets say A1:A10, and want a sum of all of the "number1"s. Some cells in that range may contain other text not in the format discussed above, in this instance the only other type of text is "OK" but in the future it could be anything. How would you go about creating such sum formula?

[Jonmo1]

How would you go about creating such sum formula?

I wouldn't.

You'd be better off with the previous formula in a 'Helper Column' (they're not a bad thing)
Then sum that column.

[FDibbins]

Seems this is the same question so carry on here

[Tony Valko]

I have a range lets say A1:A10, and want a sum of all of the "number1"s. Some cells in that range may contain other text

Try this...

Data Range

	A	B	C
1	5 of 10, missing 5		16
2	10 of 10, missing 5
3	Text
4	Text
5	1 of 10, missing 0
6	------	------	------

This array formula** entered in C1:

=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--LEFT(A1:A10,FIND(" ",A1:A10)-1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[jonagpa]

Tony Valko this works great for the Left (number1) but it doent seem to work for the mid or right. I tried reconstructing the formula for the mid. Can you let me know if I am doing something wrong?

SUM(IF(ISNUMBER(-MID(A1:A10)),--MID(A1:A10),FIND(" ",A1:A10))+4,FIND(",",A1:A10))-FIND(" ",A1:A10)1)-4)-1)))

I lack a basic understanding of the formula to begin with, is there any place I can read up on this? I know some sites have a tutorial on combinations like these.

[Tony Valko]

Tony Valko this works great for the Left (number1) but it doent seem to work for the mid or right.

You didn't say you wanted numbers 2 and/or 3.

It'll be more complicated.

I'm going out to eat so I'll be offline for several hours.

[jonagpa]

I know! sorry I thought I would be able to learn the syntax and apply it to the other numbers. Any help is appreciated, thanks!

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	5 of 10, missing 5		16	120	15
2	10 of 10, missing 10
3	Text
4	Text
5	1 of 100, missing 0
6	------	------	------	------	------

All formulas are array formulas**.

Sum of Number 1:

=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--LEFT(A1:A10,FIND(" ",A1:A10)-1)))

Sum of Number 2:

=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--MID(A1:A10,FIND("of",A1:A10)+3,FIND(",",A1:A10)-1-(FIND("of",A1:A10)+2))))

Sum of Number 3:

=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--TRIM(RIGHT(SUBSTITUTE(A1:A10," ",REPT(" ",6)),5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Tony Valko]

Sum of Number 3:

=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--TRIM(RIGHT(SUBSTITUTE(A1:A10," ",REPT(" ",6)),5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Improvement:

=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--RIGHT(SUBSTITUTE(
A1:A10," ","000000"),5)))

Still array entered!

[bebo021999]

Another option using substitute, i.e number 2:

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(A1:A6,"of",REPT(" ",100)),",",REPT(" ",100)),100,100),""))

Array formula also

[hemesh]

Already many solutions has been provided here but another one may be
in C1 as array entered below and drag to right
=SUM(INDEX(IFERROR(--(TRIM(MID(TRANSPOSE(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(--LEFT($B$1:$B$5)),$B$1:$B$5,0),"of",""),",",""),"missing","")," ",REPT(" ",20))),ROW($1:$15)*20,20))),0),MOD(COLUMN(A1)-1,3)+COLUMN(A1)))

[jonagpa]

Hello,

I've been having a hard time understanding how these formulas work. Would anyone point me towards a good tutorial? I've read a few but haven't found what I need. I would like to understand it myself so I don't have to come here and ask for every time.

I need to retrieve the numbers in a very similar way as my first request. the text is different from last time.
New text string: "99999 lb, 99999 pcs"
I need to add all of the Lbs in a range and also all the pcs in a range.

For the lbs I used the same formula given before for "number 1"
=SUM(IF(ISNUMBER(-LEFT(A1:A10)),--LEFT(A1:A10,FIND(" ",A1:A10)-1)))

I didt have any luck getting the pcs to sum.

thanks in advance.

[FlameRetired]

Separate sums again or all summed together?

[jonagpa]

separate sums again, I need the total of Lb separate from the total of pcs.

[jonagpa]

double post

[FlameRetired]

Try array entering this in B1

Please Login or Register  to view this content.

and array entering this in C1

Please Login or Register  to view this content.