Right, Mid, Len..can't seem to get what I want. 4

[ballbearing]

223832 180/2 OZ COOKIE DOUGH ALL AMERICAN HOMESTYLE 52.84
221821 200/1.5 OZ COOKIE DOUGH CHOC CHIP LOW FAT 40.08
223956 2000/1.5 OZ COOKIE DOUGH CHOC CHIP W/M&M'S 143.56
236063 240/1 OZ COOKIE DOUGH CHOC CHIP HOMESTYLE 35.23

This is what I've got in Excel. (A1, A2, A3 and A4)

I need to extract the first 6 digits (easy enough) but what is getting me is extracting the amounts at the end of the string. Different length strings and amounts throwing me a curve here. Has to be simple but...??

Thanks

 

[DataFanatic]

What happens with LEFT(A2,6) and TRIM(RIGHT(A2,6))
Assuming that the MAX value is 999.99

 

[fneily]

Assuming you mean:
223832 180/2 OZ COOKIE DOUGH ALL AMERICAN HOMESTYLE 52.84
is in cell A1, then to get 52.84 or the last numbers of any group:

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

This is from John Walkenbach's book Excel 2002 Formulas. It works, I already tried it.

 

[SkipVought]

Hi,

Try this. It assumes that the price will be within the last 8 bytes...
[tt]
=VALUE(RIGHT(RIGHT(A2,8),8-FIND(" ",RIGHT(A2,8))))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

There must be an easier way, but this works:

=VALUE(RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(SUBSTITUTE(A1," ", " "))-LEN(A1)))))

 

[ballbearing]

DataF: Works well but I do have amounts exceeding 999. Had to check that first but there are. Thanks

Fneily: I said it was probably a simple solution but

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Now WHY didn't I think of that one? I'm trying to follow how that formula works but I'm lost as soon as I get past =RIGHT. And yes you're correct..it does work mysteriously well! Thanks.

Skip...yours works great too and seems to be the winner here.

Mintj: Yes..there is an easier way...see Fneily's response. Hahaha


Thanks all for your responses!

 

[mintjulep]

Fneily,

You mean I could of looked it up in a book rather than working it out!?!?!?

 

[mintjulep]

SUBSTITUTE(A1," ", " ") -> changes every single space to a double space.

LEN(SUBSTITUTE(A1," ", " "))-LEN(A1) -> results in the number of spaces.

SUBSTITUTE(A1," ","~",LEN(SUBSTITUTE(A1," ", " "))-LEN(A1) -> changes the LAST space to a "~"

FIND("~",SUBSTITUTE(A1," ","~",LEN(SUBSTITUTE(A1," ", " "))-LEN(A1))) -> returns the location of the last space

,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(SUBSTITUTE(A1," ", " "))-LEN(A1))) -> returns the number of characters after the last space

 

[ballbearing]

Sorry, Mintj, absolutely no disrespect intended there. It's just that a formula of that complexity is way beyond my comprehension. Obviously not your's. Thanks again.

 

[strongm]

And just for fun if you are happy with macros, you can create your own function; something like:

Public Function LastNumber(srcCell As Range) As Double
LastNumber = Right(srcCell, Len(srcCell) - InStrRev(srcCell, " "))
End Function

 

[mintjulep]

None taken. I enjoyed the mental exercise.

 

[SkipVought]

or even...
Public Function LastNumber(srcCell As Range) As Double
LastNumber = Split(srcCell, " ")(ubound(Split(srcCell, " ")))
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

yep

 

[AnotherHiggins]

Just to keep the hits rollin', here are a couple more formula-based solutions.

This one is an Array Formula, so it must be entered with [Ctrl]+[Shift]+[Enter] (instead of just [enter]):
[COLOR=blue white]=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0)-1)[/color]

This next formula assumes you'll never have a string longer than 99 characters in column A. If you may have longer strings, you can adjust both occurrences of 99:
[COLOR=blue white]=MID($A1,LOOKUP(99,FIND(" ",$A1,ROW($1:$99)))+1,9)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.