|
Parsing Number from Text
I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
Try something like this:
=SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
Yes! I'm sorry....I didn't pay close enough attention at first.
You need the test applied to every cell in a range, right? Try something like this ARRAY FORMULA: =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A55)),MID(LEFT(A2:A55, SEARCH(" B/O",A2:A55)-1),SEARCH("GBP",A2:A55)+3,255))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Adjust the range references to suit your situation. (I used A2:A55) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
No need for me to be lazy, right?
Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
OK, Brian.....This is the latest in a series of final formulas :)
(ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
Thanks again Ron....but still no luck.
I tried the formula listed below (changed the A2:A300 to AL2:AL300) and still get the #VALUE! response. Some other rows have data in column AL....but I don't need any of these if they don't have that GBP string in them. Would that matter? "Ron Coderre" wrote: OK, Brian.....This is the latest in a series of final formulas :) (ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
Do other cells in Col_AL contain "GBP", but no "REFNO"
or no amount between "GBP" and "REFNO"? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks again Ron....but still no luck. I tried the formula listed below (changed the A2:A300 to AL2:AL300) and still get the #VALUE! response. Some other rows have data in column AL....but I don't need any of these if they don't have that GBP string in them. Would that matter? "Ron Coderre" wrote: OK, Brian.....This is the latest in a series of final formulas :) (ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
No....most of the other rows that have data don't have GBP in them.
Most of them are blank.....or have some other words....but not GBP "Ron Coderre" wrote: Do other cells in Col_AL contain "GBP", but no "REFNO" or no amount between "GBP" and "REFNO"? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks again Ron....but still no luck. I tried the formula listed below (changed the A2:A300 to AL2:AL300) and still get the #VALUE! response. Some other rows have data in column AL....but I don't need any of these if they don't have that GBP string in them. Would that matter? "Ron Coderre" wrote: OK, Brian.....This is the latest in a series of final formulas :) (ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
The issue probably lies in the data. So far, I only get the #VALUE! error if
the characters between "GBP" and " REFNO" contain letters or do not resolve to a valid number: Examples: These work.... GBP 100.00 REFNO GBP 00100.00 REFNO GBP100.00 REFNO These don't.... GBP a100.00 REFNO........"a" GBP 100.00REFNO...........no space before REFNO GBP 10.0.0.0 REFNO...........extra decimal points Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: No....most of the other rows that have data don't have GBP in them. Most of them are blank.....or have some other words....but not GBP "Ron Coderre" wrote: Do other cells in Col_AL contain "GBP", but no "REFNO" or no amount between "GBP" and "REFNO"? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks again Ron....but still no luck. I tried the formula listed below (changed the A2:A300 to AL2:AL300) and still get the #VALUE! response. Some other rows have data in column AL....but I don't need any of these if they don't have that GBP string in them. Would that matter? "Ron Coderre" wrote: OK, Brian.....This is the latest in a series of final formulas :) (ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
I'll double check the other lines that have GBP in them....
I thought they were all formatted the same way (ie../BNF/GBP391.38 REFNO)....but maybe I'm missing something. Thanks again for your help. "Ron Coderre" wrote: The issue probably lies in the data. So far, I only get the #VALUE! error if the characters between "GBP" and " REFNO" contain letters or do not resolve to a valid number: Examples: These work.... GBP 100.00 REFNO GBP 00100.00 REFNO GBP100.00 REFNO These don't.... GBP a100.00 REFNO........"a" GBP 100.00REFNO...........no space before REFNO GBP 10.0.0.0 REFNO...........extra decimal points Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: No....most of the other rows that have data don't have GBP in them. Most of them are blank.....or have some other words....but not GBP "Ron Coderre" wrote: Do other cells in Col_AL contain "GBP", but no "REFNO" or no amount between "GBP" and "REFNO"? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks again Ron....but still no luck. I tried the formula listed below (changed the A2:A300 to AL2:AL300) and still get the #VALUE! response. Some other rows have data in column AL....but I don't need any of these if they don't have that GBP string in them. Would that matter? "Ron Coderre" wrote: OK, Brian.....This is the latest in a series of final formulas :) (ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific.... Here's the exact wording that's in one of my AL cells. REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN EXPRESS LTD Would this alter the formula? Thanks again! Here's the "Ron Coderre" wrote: No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2: AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells to search? My data in column AL can range from row 2 to row 300. How can I factor this into the forumla? "Ron Coderre" wrote: Try something like this: =SUM(MID(LEFT(AL19,SEARCH(" B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( " B/O",AL50)-1),SEARCH("GBP",AL50)+3,255)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Brian" wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? |
Parsing Number from Text
On Thu, 5 Apr 2007 11:20:05 -0700, Brian
wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? Try this: 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr It can be embedded in the workbook if that is an issue. Then use this **array** formula (enter with <ctrl<shift<enter) =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP).*?(?=\s|$)")) where "rng" is the range that contains your strings. --ron |
Parsing Number from Text
On Thu, 05 Apr 2007 20:22:48 -0400, Ron Rosenfeld
wrote: On Thu, 5 Apr 2007 11:20:05 -0700, Brian wrote: I have multiple columns and rows of data. In some of my rows, there will be data in column AL,that contains a line like the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX Is there a forumla I can use that will add up all the figures that follow the "GBP" in column AL? For example: In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX I'd like a formula that will give me the result : 37,629.52 (the addition of the numbers following the GBP in column AL) Any suggestions? Try this: 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr It can be embedded in the workbook if that is an issue. Then use this **array** formula (enter with <ctrl<shift<enter) =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP).*?(?=\s|$)")) where "rng" is the range that contains your strings. --ron This might be more foolproof: =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+")) --ron |
Parsing Number from Text
Ron Rosenfeld wrote...
.... This might be more foolproof: =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+")) This fails with Ron Coderre's pathological samples, These work.... GBP 100.00 REFNO GBP 00100.00 REFNO GBP100.00 REFNO These don't.... GBP a100.00 REFNO........"a" GBP 100.00REFNO...........no space before REFNO GBP 10.0.0.0 REFNO...........extra decimal points IMO, you need to use REGEX.SUBSTITUTE to do this. REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]") |
Parsing Number from Text
Hmmm..."pathological"
Interesting choice of word, Dick Per Merriam-Webster: "being such to a degree that is extreme, excessive, or markedly abnormal" Exactly what I was trying to demonstrate....that perhaps the structure of the text wasn't as rigid as we might have thought, ultimately requiring a much more arcane solution....as you so aptly provided. *********** Regards, Ron XL2002, WinXP "Harlan Grove" wrote: Ron Rosenfeld wrote... .... This might be more foolproof: =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+")) This fails with Ron Coderre's pathological samples, These work.... GBP 100.00 REFNO GBP 00100.00 REFNO GBP100.00 REFNO These don't.... GBP a100.00 REFNO........"a" GBP 100.00REFNO...........no space before REFNO GBP 10.0.0.0 REFNO...........extra decimal points IMO, you need to use REGEX.SUBSTITUTE to do this. REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]") |
Parsing Number from Text
On 5 Apr 2007 18:31:29 -0700, "Harlan Grove" wrote:
Ron Rosenfeld wrote... ... This might be more foolproof: =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+")) This fails with Ron Coderre's pathological samples, These work.... GBP 100.00 REFNO GBP 00100.00 REFNO GBP100.00 REFNO These don't.... GBP a100.00 REFNO........"a" GBP 100.00REFNO...........no space before REFNO GBP 10.0.0.0 REFNO...........extra decimal points IMO, you need to use REGEX.SUBSTITUTE to do this. REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]") You're correct. As written, the REGEX.MID (my 2nd one) would handle the issue of no space before REFNO. However, I did make the assumption that there was no space between GPB and the value to be extracted; and that that value did not contain multiple decimals, and that it was a positive value. It would be easy to add in the option for an optional space between GBP and the value: =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\s\d\.]+")) and one could even handle the possibility of signed numbers and multiple decimals by using a more general notation for floating point numbers: =SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)\s*[-+]?\d*\.?\d+")) But to handle the possibility of extraneous characters between GBP and the number, like the "a" above, I would absolutely agree that the REGEX.SUBSTITUTE would be better. --ron |
| All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com