|
Need help on Excel formula to find an occurrence and display numbers
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 |
Need help on Excel formula to find an occurrence and display numbe
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 |
Need help on Excel formula to find an occurrence and display numbe
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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. |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
Need help on Excel formula to find an occurrence and display n
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 |
| All times are GMT +1. The time now is 11:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com