Need help on Excel formula to find an occurrence and display numbers

[pattyjamas]

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

 

[Guest]

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

 

[pattyjamas]

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











- 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

 

[Guest]

Try this:

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

 

[Guest]

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

 

[pattyjamas]

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












- 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

 

[Guest]

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

 

[pattyjamas]

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









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

 

[pattyjamas]

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

 

[T. Valko]

Try this:

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

Biff

 

[pattyjamas]

Try this:

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

Biff







- 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

 

[Ragdyer]

You're welcome Biff!<vbg>

BTW ... another prime example of the "virtues" of bottom posting ! ! !<bg>

 

[T. Valko]

Ron did the "dirty work" !

Biff

You're welcome Biff!<vbg>

BTW ... another prime example of the "virtues" of bottom posting ! ! !<bg>

 

[Ragdyer]

Tongue-in-cheek!<g>