Formula for summing addresses within a single cell

B

BW

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

smartin

BW said:
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.
 
A

Ashish Mathur

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top