You're welcome Biff!<vbg

BTW ... another prime example of the "virtues" of bottom posting ! ! !<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
oups.com...
On May 12, 6:17 pm, "T. Valko" wrote:
Try this:

=TRIM(RIGHT(B1,LEN(B1)-FIND(CHAR(7),
SUBSTITUTE(TRIM(B1)," ",CHAR(7),LEN(TRIM(B1))
-LEN(SUBSTITUTE(B1," ",""))))))

Biff

wrote in message

oups.com...



On May 12, 5:30 pm, wrote:
On May 12, 5:03 pm, Ron Coderre

wrote:

Try this:

=TRIM(RIGHT(B1,LEN(B1)-SEARCH(CHAR(7),SUBSTITUTE(B1,"
",CHAR(7),LEN(TRIM(B1))-LEN(SUBSTITUTE(B1," ",""))))))

(Watch out for text wrap)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
On May 12, 1:58 pm, Ron Coderre

wrote:
I think I see what happened.....text wrap impacted the display.

In your formula, you should NOT have a "" symbol (greater
than).

Remove that and the formula should work fine.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
On May 12, 11:06 am, Ron Coderre
wrote:
With your sample text
A1: 1Z-31245 Coiled Springs 041,779 0 0 41,779

Try this:
B1: =RIGHT(A1,LEN(A1)-SEARCH(CHAR(7),SUBSTITUTE(A1,"
",CHAR(7),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

" wrote:
Thanks in advance. I apologize for asking this question

I have a cell with the following in it: 1Z-31245 Coiled
Springs 0
41,779 0 0 41,779

I am using the below formula to return 41,779 it
searched
for the
occurrence of "0 " but I rather use a formula that a)
finds
the string
length and then b) returns perhaps the last numbers from
the
end
(including the comma) up to the first space it
encounters.

=(TRIM(MID($B$5,FIND("0 ",$B$5,34)+1,12)))

I have a few cells with this type of text in it and the
numbers can
vary in length. What they all have in common is a "0 "
before
them. I
know it is better to search from the end backwards.

Thanks in advance for any help

Sincerely
Patty- Hide quoted text -

- Show quoted text -

Thanks. Wow. Thought there was an easier way to search FROM
THE
END
BACKWARDS left to the first occurrence of "0 " and then
return
from
there to the end.
But I guess you are doing that by finding the first space.

I receive a Value error.

I have this in B1: 5PY-s37032 Reduce12 Bxxkg 0 41,779 0 0
41,779

In A1 I tried your formula: =RIGHT(B1,LEN(B1)-
SEARCH(CHAR(7),SUBSTITUTE(B1,"
",CHAR(7),LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))

got a value error and tried to troubleshoot with little luck.
Sorry.

Hoping to return 41,779

Thanks again
Patty- Hide quoted text -

- Show quoted text -

Ron, almost perfect.
Actually the string contains a space at the end so with that
space it
renders no result. If I add a space it will provide the right
result.
Tried tweaking it myself (idiot) but did not have success.

Most of the strings will have a trailing space at the end.
Could you give it one more shot for me with a trailing space at
the
end of the line:

5PY-s37032 Reduce12 Bxxkg 0 41,779 0 0 41,779

Thanks
Sorry
Patty- Hide quoted text -

- Show quoted text -

Ron,
Well how close we are.
I have a few lines that are as follows-note where spaces are at
beginning or end:
1st line has no space b4 it. All other lines do.
All lines seem to end in an extra space.
5PY-10240 CMA (157,304) 189,093 569,708 0 101,497
5PY-222 XMA 1 11,853 91,847 0 103,701
DDD-3333 BBBBB 0 41,779 0 0 41,779
FFFFFFF JJJ (46,669) 48,782 206,446 0 108,559

Your formula returned the following:
101,497 (this is fine)
0 103,701 (need to remove leading 0 space)
0 41,779
0 108,559

So almost there.... Do we do another clean or a trim???

If you do not care to help any more I understnad. I can go to
Experts-
Exchange.com which I pay for.

Thanks
Patty

PS: This is actually something I am working on for my mother to see
if
she can afford a nursing home.- Hide quoted text -

- Show quoted text -

I meant Ron, not Ros-was speaking to my frined Ros on the phone while
typing.
Patty- Hide quoted text -

- Show quoted text -

Perfecto. Thank you so much.
Now I can do the rest of my work for my mother.
Have a great weekend Ron

Patty