Formula for summing addresses within a single cell

  • Thread starter Thread starter BW
  • Start date Start date
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.
 
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

Similar Threads

excel sum function 2
Excel How to add numbers in same cell? 2
Parsing one part of an address 4
Automatic update to SUM formula cell references. 6
Sum of unique values within a range with blank cells 3
Formula to sum blank cells 2
Sum if cell = grey 4
IF Function error #VALUE 2

Back
Top