Hi,



I have this data in my rows:

1. BBY TOYS SJC 15
2. BBY TOYS CUP 25
3. BBY TOYS SKU(15X10)10
4. SKU BOX 1000
5. SKU BOX 10000

I need to dynamically retrieve the following from each:

1. 15
2. 25
3. 10
4. 1000
5. 10000

I used the following formula:
=RIGHT(<CellNo>,LEN(<CellNo>)-FIND(" ",<CellNo>,10))

It works for all the above rows except the 3rd, for which it retrieves: SKU(15X10)10
Can you please help? Is there a better formula that I can use?

Thanks

You could use the following array formula (input with Ctrl-Shift-Enter):
=IF(ISERROR(RIGHT(MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1),LEN(A1)-FIND(")",A1))),MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1),RIGHT(MID(A1,MIN(IF(IS NUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1),LEN(A1)-FIND(")",A1)))

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul, wondering if there's a shorter way?

If it's just about to separate numbers from text on the right side:
=VALUE(RIGHT(A2,SUM(--ISNUMBER(--RIGHT(A2,COLUMN(INDIRECT("1:1")))))))
Change the function RIGHT to LEFT to separate numeric parts on the left side of text.

Edit: Of course it's an arrayformula as well

Much better!

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

What do the double dashes as in "--ISNUMBER" do?

gebobs, the double minus signs (or double dashes) just replace a multiplication by one.
That way the formula just does the same:
=VALUE(RIGHT(A2,SUM(1*ISNUMBER(1*RIGHT(A2,COLUMN(INDIRECT("1:1")))))))