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

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

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

Try this:

=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,99)



" 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

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

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

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

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 -

Ros,
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.

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

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

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

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

Ron did the "dirty work" !

Biff

"Ragdyer" wrote in message
...
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

Tongue-in-cheek!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
Ron did the "dirty work" !

Biff

"Ragdyer" wrote in message
...
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