Learning arrays.
I am using the basic Excel help tool within my 2002 version to investigate
how arrays operate. Specifically, I started with the ROW() function. The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. I tried doing the exact same thing in Open Office Calc and it worked perfectly. Is there some setting or feature that needs to be changed or added in Excel? By the way, the array curly brackets do get placed around the formula after I hit Ctrl-Shift-Enter. Thanks for your reply. -- David Farber Los Osos, CA |
Learning arrays.
David Farber pretended :
I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. Specifically, I started with the ROW() function. The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. I tried doing the exact same thing in Open Office Calc and it worked perfectly. Is there some setting or feature that needs to be changed or added in Excel? By the way, the array curly brackets do get placed around the formula after I hit Ctrl-Shift-Enter. Thanks for your reply. Formulas *always* must begin with *=* sign -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Learning arrays.
GS wrote:
David Farber pretended : I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. Specifically, I started with the ROW() function. The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. I tried doing the exact same thing in Open Office Calc and it worked perfectly. Is there some setting or feature that needs to be changed or added in Excel? By the way, the array curly brackets do get placed around the formula after I hit Ctrl-Shift-Enter. Thanks for your reply. Formulas *always* must begin with *=* sign Yes, you are correct. I did put in the equals sign in the spreadsheet. For some reason, I thought that would have been implied in my question based on my results but I should have known better. Still looking for the answer. Thanks for your reply. -- David Farber Los Osos, CA |
Learning arrays.
David Farber formulated the question :
GS wrote: David Farber pretended : I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. Specifically, I started with the ROW() function. The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. I tried doing the exact same thing in Open Office Calc and it worked perfectly. Is there some setting or feature that needs to be changed or added in Excel? By the way, the array curly brackets do get placed around the formula after I hit Ctrl-Shift-Enter. Thanks for your reply. Formulas *always* must begin with *=* sign Yes, you are correct. I did put in the equals sign in the spreadsheet. For some reason, I thought that would have been implied in my question based on my results but I should have known better. Still looking for the answer. Thanks for your reply. Array formulas can be entered in a *single* cell then *copied* to any other cells that you want to use the array formula in, *OR* you can select a contiguous range of cells to enter the same formula in all the selected cells. The difference in behavior is as follows... When I *select A1:A3* and enter "=C4:D6" as an array formula, those 3 cells populate with 4,5,6 respectively. These cells all ref "C4:D6". If you enter the same array formula in B1 (single cell) and copy it down to B2/B3, those 3 cells populate with the same results (4,5,6 respectively) *but* the refs change to "C5:D7" in B2 and "C6:D8" in B3. I suspect that you did not select the 2 cells that you were expecting "5,6" to populate. Note that formulas can not change other cells, only the cells that contains the formula. So if C5/C6 are empty then your formula did not populate them when you did Ctrl+Shift+Enter. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Learning arrays.
"David Farber" wrote:
The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. You probably overlooked the subtle instructions. According to the Help page, after you normally-enter (just press Enter as usual) the formula into A2, the instructions ask you to select A2:A4, press F2, then press ctrl+shift+Enter to array-enter the formula. But please note: that method is nonsensical. Sometimes the help pages show you how to do things in order to demonstrate form or method. That does not mean it is the best way to accomplish the task. First, it is nonsensical to use ROW(C4:D6) in that context. Since we are entering the formula into a single column, the ROW parameter should be a single column, e.g. ROW(C4:C6). Second, it is nonsensical to refer to another column unnecessarily, C4:C6 in this case. And that goes double for original range C4:D6. The problem that causes is: it creates a dependency on those cells. So whenever any of C4:C6 is modified, A2:A4 will be recalculated. Sometimes that is our intent. And even if it isn't, it is not a big deal in this very simple case. But generally, it could be a big deal when ROW(C4:C6) is part of a more complicated and time-consuming formula, e.g. a lookup operation. So ROW(A4:A6) would be a better choice in this context. Finally, it is nonsensical to use an array-entered formula at all in this particular example. Simply normally-enter =ROW(A4) into A2, then copy the formula down through A6. I avoid array-entered formulas, especially multi-cell array-entered formulas. They are difficult to modify. For example, if you had array-entered =ROW(A4:A6) into A2:A4, then tried to replace the formula in A2 with =ROW(A4) as I suggested, you probably encountered an error to the effect "cannot change part of an array". To avoid the error, we must first select A2:A4 and delete the formula. |
Learning arrays.
"joeu2004" wrote in message ... "David Farber" wrote: The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. You probably overlooked the subtle instructions. According to the Help page, after you normally-enter (just press Enter as usual) the formula into A2, the instructions ask you to select A2:A4, press F2, then press ctrl+shift+Enter to array-enter the formula. But please note: that method is nonsensical. Sometimes the help pages show you how to do things in order to demonstrate form or method. That does not mean it is the best way to accomplish the task. First, it is nonsensical to use ROW(C4:D6) in that context. Since we are entering the formula into a single column, the ROW parameter should be a single column, e.g. ROW(C4:C6). Second, it is nonsensical to refer to another column unnecessarily, C4:C6 in this case. And that goes double for original range C4:D6. The problem that causes is: it creates a dependency on those cells. So whenever any of C4:C6 is modified, A2:A4 will be recalculated. Sometimes that is our intent. And even if it isn't, it is not a big deal in this very simple case. But generally, it could be a big deal when ROW(C4:C6) is part of a more complicated and time-consuming formula, e.g. a lookup operation. So ROW(A4:A6) would be a better choice in this context. Finally, it is nonsensical to use an array-entered formula at all in this particular example. Simply normally-enter =ROW(A4) into A2, then copy the formula down through A6. I avoid array-entered formulas, especially multi-cell array-entered formulas. They are difficult to modify. For example, if you had array-entered =ROW(A4:A6) into A2:A4, then tried to replace the formula in A2 with =ROW(A4) as I suggested, you probably encountered an error to the effect "cannot change part of an array". To avoid the error, we must first select A2:A4 and delete the formula. I think the help section intended the example to be more instructional than practically useful. Let me just finish this part of my question by saying that the function works perfectly well in Open Office's Calc. See image he http://webpages.charter.net/mrfixite...rayExample.jpg Maybe we can sidestep this whole discussion and let me explain why I am even researching this topic. I was interested in converting a Word document which contained a contact list into Excel format. This list was to be used in a mail merge to print labels. Long story short, I imported the list into Excel and that worked fine except the City State and Zip were all included in one field. I found many ways to break this into separate fields/columns but the method I liked most required commas between the city and state and the data document didn't have commas inserted in those positions. So I found this link with this formula at : http://www.excelforum.com/showthread.php?t=342639&p=876198&viewfull=1#post87 6198=MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789"))-2-(SEARCH(",",A1)+1))....confirmed with CONTROL+SHIFT+ENTER.I was curious to know how that expression was evaluated and how I couldmodify it to search for any digit, 0-9 within the CityStateZip string. Ithought I would be able to locate where the zip code began in the string andsubsequently I could count spaces backward to find the state and then thecity. This could all be accomplished because there are no cities or stateswhich contain numbers in their names.Thanks for your reply.--David FarberLos Osos, CA |
Learning arrays.
GS wrote:
David Farber formulated the question : GS wrote: David Farber pretended : I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. Specifically, I started with the ROW() function. The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. I tried doing the exact same thing in Open Office Calc and it worked perfectly. Is there some setting or feature that needs to be changed or added in Excel? By the way, the array curly brackets do get placed around the formula after I hit Ctrl-Shift-Enter. Thanks for your reply. Formulas *always* must begin with *=* sign Yes, you are correct. I did put in the equals sign in the spreadsheet. For some reason, I thought that would have been implied in my question based on my results but I should have known better. Still looking for the answer. Thanks for your reply. Array formulas can be entered in a *single* cell then *copied* to any other cells that you want to use the array formula in, *OR* you can select a contiguous range of cells to enter the same formula in all the selected cells. The difference in behavior is as follows... When I *select A1:A3* and enter "=C4:D6" as an array formula, those 3 cells populate with 4,5,6 respectively. These cells all ref "C4:D6". If you enter the same array formula in B1 (single cell) and copy it down to B2/B3, those 3 cells populate with the same results (4,5,6 respectively) *but* the refs change to "C5:D7" in B2 and "C6:D8" in B3. I suspect that you did not select the 2 cells that you were expecting "5,6" to populate. Note that formulas can not change other cells, only the cells that contains the formula. So if C5/C6 are empty then your formula did not populate them when you did Ctrl+Shift+Enter. Starting with a blank worksheet, when I selected cells A1:A3 and entered "=C4:D6" as an array formula, those 3 cells populate with zeroes. Was I supposed to use the ROW() function there? If I do, I at least get a 4 to appear in A1. The others are still zeroes. When I entered the same array formula in B1 (single cell) and copied it down to B2/B3, those 3 cells populate with zeroes. The refs did change to "C5:D7" in B2 and "C6:D8" in B3. Same question as above regarding the ROW() function. Note that formulas can not change other cells, only the cells that contains the formula. So if C5/C6 are empty then your formula did not populate them when you did Ctrl+Shift+Enter. I agree with you about the part that C5/C6 are empty but I'm not sure what conclusion you have come to about that. Could something be amiss with the way Excel is interpreting my "Ctrl-Shift-Enter?" Thanks for your reply. -- David Farber Los Osos, CA |
Learning arrays.
David Farber wrote:
GS wrote: David Farber formulated the question : GS wrote: David Farber pretended : I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. Specifically, I started with the ROW() function. The Excel help says that if you put, ROW() into a cell, I used C4, then the row number should appear and that does work. In the second example it says that if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the formula should populate with 4, 5, 6. What happens when I try this is that I only get a 4 displayed in the cell, C4, where the formula is. The other cells below are left blank. I tried doing the exact same thing in Open Office Calc and it worked perfectly. Is there some setting or feature that needs to be changed or added in Excel? By the way, the array curly brackets do get placed around the formula after I hit Ctrl-Shift-Enter. Thanks for your reply. Formulas *always* must begin with *=* sign Yes, you are correct. I did put in the equals sign in the spreadsheet. For some reason, I thought that would have been implied in my question based on my results but I should have known better. Still looking for the answer. Thanks for your reply. Array formulas can be entered in a *single* cell then *copied* to any other cells that you want to use the array formula in, *OR* you can select a contiguous range of cells to enter the same formula in all the selected cells. The difference in behavior is as follows... When I *select A1:A3* and enter "=C4:D6" as an array formula, those 3 cells populate with 4,5,6 respectively. These cells all ref "C4:D6". If you enter the same array formula in B1 (single cell) and copy it down to B2/B3, those 3 cells populate with the same results (4,5,6 respectively) *but* the refs change to "C5:D7" in B2 and "C6:D8" in B3. I suspect that you did not select the 2 cells that you were expecting "5,6" to populate. Note that formulas can not change other cells, only the cells that contains the formula. So if C5/C6 are empty then your formula did not populate them when you did Ctrl+Shift+Enter. Starting with a blank worksheet, when I selected cells A1:A3 and entered "=C4:D6" as an array formula, those 3 cells populate with zeroes. Was I supposed to use the ROW() function there? If I do, I at least get a 4 to appear in A1. The others are still zeroes. When I entered the same array formula in B1 (single cell) and copied it down to B2/B3, those 3 cells populate with zeroes. The refs did change to "C5:D7" in B2 and "C6:D8" in B3. Same question as above regarding the ROW() function. Note that formulas can not change other cells, only the cells that contains the formula. So if C5/C6 are empty then your formula did not populate them when you did Ctrl+Shift+Enter. I agree with you about the part that C5/C6 are empty but I'm not sure what conclusion you have come to about that. Could something be amiss with the way Excel is interpreting my "Ctrl-Shift-Enter?" Thanks for your reply. Ok, I re-read your instructions again. When I preselect the range (and use the ROW() function) before I enter the formula, then it works. And that goes to your comment that it cannot change the contents of cells that aren't selected. I hope I interpreted that correctly. I guess the Open Office version works under a different set of rules. Thanks for your reply. -- David Farber Los Osos, CA |
Learning arrays.
"David Farber" wrote:
I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. [....] I guess the Open Office version works under a different set of rules. Open Office?! You started by claiming you are asking about MICROSOFT Excel. Now you say you are asking about APACHE Open Office. That is a different animal altogether. Any similarity in behavior is completely accidental. (Albeit perhaps by design.) |
Learning arrays.
joeu2004 wrote:
"David Farber" wrote: I am using the basic Excel help tool within my 2002 version to investigate how arrays operate. [....] I guess the Open Office version works under a different set of rules. Open Office?! You started by claiming you are asking about MICROSOFT Excel. Now you say you are asking about APACHE Open Office. That is a different animal altogether. Any similarity in behavior is completely accidental. (Albeit perhaps by design.) In my original message I said, "...I tried doing the exact same thing in Open Office Calc and it worked perfectly. " My thinking was at least some part of my logic had to be correct. (-: Thanks for your reply. -- David Farber Los Osos, CA |
Learning arrays.
"David Farber" wrote:
In my original message I said, "...I tried doing the exact same thing in Open Office Calc and it worked perfectly. " Ah, yes. I went looking for such a reference before posting, but I missed it. To be clear: you are indeed asking about Microsoft Excel. I'm not sure what the mystery still is. I pointed to the subtle instructions in the ROW help page. You can enter =ROW(C4:D6) into C4 (although =ROW(C4:C6) makes more sense for your purposes), then select C4:C6, press F2, then press ctrl+shift+Enter. Or with forethought, you can select C4:C46, type =ROW(C4:D6), then press ctrl+shift+Enter. As I noted before, I suspect you simply typed =ROW(C4:D6) in C4, then pressed ctrl+shift+Enter. Of course, at that point, Excel does not know how many rows to propagate the array formula into. That is why you see only 4 in C4. I would be surprised the Apache Open Office spreadsheet (whatever it is called) knows any better either, when you make the same mistake. But anything is possible in software. Be that as it may, I don't see how this exercise (and unnecessary way to accomplish the same result with ROW per se) helps with your original problem or curiosity. To wit.... "David Farber" wrote elsewhere (edited): I found this link with this formula at : =MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4,5,6 ,7,8,9},A1&"0123456789")) -2-(SEARCH(",",A1)+1))....confirmed with CONTROL+SHIFT+ENTER. I was curious to know how that expression was evaluated Since SEARCH takes only a single-valued expression for the first parameter, array-entering the formula cause the evaluation of the 2nd SEARCH expression to create an array. In effect, it is as you had written (if we could, which we cannot): MIN({SEARCH(0,A1&"0123456789"),SEARCH(1,A1&"012345 6789"),...,SEARCH(9,A1&"0123456789")}) By appending "0123456789", the SEARCH will always succeed. That is needed so that MIN does not propagate the Excel error that SEARCH would return if it failed. Thus, MIN sees an array of indexes (character positions) in A1&"0123456789". By returning the smallest such index, MIN returns the position of the first number in A1, if any. Caveat: Microsoft Excel is inconsistent with where it permits the use such "array expressions". For example, SUMPRODUCT(VLOOKUP(A1:A10,Table,2)) should be evaluated as if we had written SUMPRODUCT({VLOOKUP(A1,Table,2),VLOOKUP(A2,Table,2 ),...,VLOOKUP(A10,Table,2)), returning the sum of the 10 lookup results. But it does not :-(. The point is: Always test any array-entered formula to be sure it is doing what you intended. And avoid them when you can. |
Learning arrays.
joeu2004 wrote:
"David Farber" wrote: In my original message I said, "...I tried doing the exact same thing in Open Office Calc and it worked perfectly. " Ah, yes. I went looking for such a reference before posting, but I missed it. To be clear: you are indeed asking about Microsoft Excel. I'm not sure what the mystery still is. I pointed to the subtle instructions in the ROW help page. You can enter =ROW(C4:D6) into C4 (although =ROW(C4:C6) makes more sense for your purposes), then select C4:C6, press F2, then press ctrl+shift+Enter. Or with forethought, you can select C4:C46, type =ROW(C4:D6), then press ctrl+shift+Enter. As I noted before, I suspect you simply typed =ROW(C4:D6) in C4, then pressed ctrl+shift+Enter. Of course, at that point, Excel does not know how many rows to propagate the array formula into. That is why you see only 4 in C4. I would be surprised the Apache Open Office spreadsheet (whatever it is called) knows any better either, when you make the same mistake. But anything is possible in software. Be that as it may, I don't see how this exercise (and unnecessary way to accomplish the same result with ROW per se) helps with your original problem or curiosity. To wit.... "David Farber" wrote elsewhere (edited): I found this link with this formula at : =MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4,5,6 ,7,8,9},A1&"0123456789")) -2-(SEARCH(",",A1)+1))....confirmed with CONTROL+SHIFT+ENTER. I was curious to know how that expression was evaluated Since SEARCH takes only a single-valued expression for the first parameter, array-entering the formula cause the evaluation of the 2nd SEARCH expression to create an array. In effect, it is as you had written (if we could, which we cannot): MIN({SEARCH(0,A1&"0123456789"),SEARCH(1,A1&"012345 6789"),...,SEARCH(9,A1&"0123456789")}) By appending "0123456789", the SEARCH will always succeed. That is needed so that MIN does not propagate the Excel error that SEARCH would return if it failed. Thus, MIN sees an array of indexes (character positions) in A1&"0123456789". By returning the smallest such index, MIN returns the position of the first number in A1, if any. Caveat: Microsoft Excel is inconsistent with where it permits the use such "array expressions". For example, SUMPRODUCT(VLOOKUP(A1:A10,Table,2)) should be evaluated as if we had written SUMPRODUCT({VLOOKUP(A1,Table,2),VLOOKUP(A2,Table,2 ),...,VLOOKUP(A10,Table,2)), returning the sum of the 10 lookup results. But it does not :-(. The point is: Always test any array-entered formula to be sure it is doing what you intended. And avoid them when you can. Finally, I think we're on the same wavelength now. The initial mystery is mostly solved. That was that I needed to highlight the cells where the data was going to be displayed before entering the formula. The other part of the mystery was how the other software, Apache's Calc, was able to do this without prompting. For now, I'm just going to concentrate on Excel. The reason I chose to use this as a starting point to figure out arrays was it seemed like a very simple example of how arrays are created. I didn't expect that I would be writing several lengthy messages trying to figure it out. Now back to the big picture. I like how you explained this: Since SEARCH takes only a single-valued expression for the first parameter, array-entering the formula cause the evaluation of the 2nd SEARCH expression to create an array. In effect, it is as you had written (if we could, which we cannot): MIN({SEARCH(0,A1&"0123456789"),SEARCH(1,A1&"012345 6789"),...,SEARCH(9,A1&"0123456789")}) Now my question is, which I have been unable to figure out by experimentation, what is the correct result of: SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") assuming for example that A1 contains a city, state, and zip? You said it would produce an array. That makes perfect sense to me but do I have to select a group of cells as before and then enter the formula? And how should it be entered into the formula box? Do I use Ctrl-Shift-Enter to add braces even though there are already braces around the search text? Every way I try it, I can only get a single number returned. I was hoping a nice array would have been produced somewhere. Just to be perfectly clear, I chose as my city, state, zip field, "anytown, ca, 91234" and the search result returns 19 which is where the first 0 appears in the concatenated string, "anytown, ca 91234012345679" Thanks for your reply. -- David Farber Los Osos, CA |
Learning arrays.
"David Farber" wrote:
Now my question is, which I have been unable to figure out by experimentation, what is the correct result of: SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") assuming for example that A1 contains a city, state, and zip? You said it would produce an array. And it does. But if you simply array-enter (press ctrl+shift+Enter): =SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") into a single cell, Excel assumes you want to select a single element of the array, the first element in this case. You should have array-entered (press ctrl+shift+Enter): =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ) It is MIN that knows what to do with the array. If you want to see the array returned by array-entering that SEARCH expression, use the Evaluate Formula operation. (It is available in Excel 2003. I don't know about Excel 2002.) Alternatively, select B1:K1 (a row of 10 cells, not a column), type the following formula: =SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") then press ctrl+shift+Enter. |
Learning arrays.
joeu2004 wrote:
"David Farber" wrote: Now my question is, which I have been unable to figure out by experimentation, what is the correct result of: SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") assuming for example that A1 contains a city, state, and zip? You said it would produce an array. And it does. But if you simply array-enter (press ctrl+shift+Enter): =SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") into a single cell, Excel assumes you want to select a single element of the array, the first element in this case. You should have array-entered (press ctrl+shift+Enter): =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ) It is MIN that knows what to do with the array. If you want to see the array returned by array-entering that SEARCH expression, use the Evaluate Formula operation. (It is available in Excel 2003. I don't know about Excel 2002.) Alternatively, select B1:K1 (a row of 10 cells, not a column), type the following formula: =SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") then press ctrl+shift+Enter. That was easy. (-; Worked like a charm. From there, I was able to save the search value, I'll call it n, in a hidden column to make the subsequent formulas much shorter. Then I used that value to construct another string which started at the n-4th character (there are two spaces between state and zipcode) and was two characters long for state. For the city, I started at the beginning of the string and ended it at the n-5th character. In case anyone is interested, this does work in Apache's Open Office Calc except function arguments and array values are separated by semicolons, not commas. Thanks for your great help. -- David Farber Los Osos, CA |
All times are GMT +1. The time now is 02:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com