count how many numbers in a range

[rp1783]

I have 2 columns. Column A contains a phone number i.e. 213-555-1234, column B contains the last 4 digits of the last number in the phone number range i.e. 1264. I'm looking to add a third column. I would like column C to have the total number of phone numbers. In the examples provided this would be 31. Any idea what formula I should be using?

[daffodil11]

Please Login or Register  to view this content.

[rp1783]

This worked great. Thank you.

Follow up question, and this may need to go in the VBA section, but is there a way I can run a macro to show all of the numbers in the ranges. I have about 150 rows where column A is the 10 digits, and column B is 4 digits (which are the last 4 in the range). Is there something I can run to output each individual number?

[daffodil11]

You could always just drag that formula down for each respective row.

If that's not what you're talking about, I'd recommend slapping an example together and posting it here.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I can write some VBA as well, if you post the attachment in the next 45 minutes.

[rp1783]

I've attached an example. Sheet 1 shows the data I have times about 150 rows. Sheet 2 shows the output I'm looking for.

[daffodil11]

How about this:

Please Login or Register  to view this content.

And with notations:

Please Login or Register  to view this content.

[rp1783]

Getting an error runtime 6 overflow error.

[daffodil11]

Sounds like maybe you have more than 150.

change integer to long

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Integer data types only extend from -32k to 32k

[rp1783]

Ok, so it worked for the first row, but the sheet I have has many rows. I should have been more clear. I've attached another example where sheet 2 shows the output of what I'm trying to accomplish.

[daffodil11]

I should really read up on my variables more.

We'll just use a double. I'm apparently bad at counting.

Please Login or Register  to view this content.

[rp1783]

YES!!! works great!!! Thank you!

[daffodil11]

Awesome. Glad I could help out.

[rp1783]

So one more question. some of the numbers in column B had preceding zeroes (so column B may only have 2 or 3 digits). These are not pulling over to sheet, and in fact, none of the numbers in that range are. I tried to format those specific cells to add the zero(es), but that didn't seem to work. Any suggestions?

[daffodil11]

Heck yeah.

We alter the concatenation line to reformat the Column B cell value as text. In Spreadsheet world it looks like =Text(reference,"0000"). In VBA it's Format(reference,"0000")

Please Login or Register  to view this content.