Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for summing addresses within a single cell
I need a formula that will sum the number of addresses listed in a cell. An
example of how they are entered is: 2550 Elm, 100 Main, 475 Maple, 702 Twin Oaks There could be any number from 1 to 50 addresses in any given cell. I'm using Excel 2003. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for summing addresses within a single cell
BW wrote:
I need a formula that will sum the number of addresses listed in a cell. An example of how they are entered is: 2550 Elm, 100 Main, 475 Maple, 702 Twin Oaks There could be any number from 1 to 50 addresses in any given cell. I'm using Excel 2003. If it will suffice to count the number of commas plus 1 you can use this formula: =1+SUMPRODUCT(--(MID(A1,COLUMN(A:IV),1)=",")) Or this array* formula: =1+SUM(IF(MID(A1,COLUMN(A:IV),1)=",",1)) *Commit the array formula by pressing Ctrl+Shift+Enter, do not just press Enter or Tab. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for summing addresses within a single cell
Hi,
Assuming that all addresses are separated by commas (and there are no other commas), use the following. Cell B25 holds the addresses. =(LEN(B25)-LEN(SUBSTITUTE(B25,",","")))+1 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "BW" wrote in message ... I need a formula that will sum the number of addresses listed in a cell. An example of how they are entered is: 2550 Elm, 100 Main, 475 Maple, 702 Twin Oaks There could be any number from 1 to 50 addresses in any given cell. I'm using Excel 2003. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing 2 to 8 separate numbers in a single cell | Excel Worksheet Functions | |||
What formula will eliminate the cell with #N/A when summing? | Excel Worksheet Functions | |||
How to Import multiple-line addresses from Word into single cell? | Excel Worksheet Functions | |||
How do I convert a single column of addresses into rows for export | Excel Discussion (Misc queries) | |||
single cell formula | New Users to Excel |