traveler84
New Member
- Joined
- Feb 17, 2020
- Messages
- 12
- Office Version
- 365
- 2016
- Platform
- Windows
Off and on for many years, I've battled an issue and I'm hoping someone might have more ridges in their brain than me.
I have a large list of phone numbers in order. What I need to do is group consecutive ranges into a format of ##########-####. Everything is standard in column A with normal formatting with no fancy codes. Literally just the phone numbers.
Example:
5550000000
5550001111
5550001112
5550001114
5550001115
Output:
5550000000
5550001111-1112
5550001114-1115
I have thousands of phone numbers that I have to find those breaks. I often cheat a little by using a =IF(A2-A1=1),"RANGE","NOT RANGE." Or I might use MID to do the same with the last 4 values depending on my flavor of the day.
My experiments:
I've figured out how to identify if a number is consecutive and flag it as such.
I have used conditional formatting, copied into WORD and back into excel, then used a VBA formula to find where each break is in the list of TNs by identifying the color code.
Issue:
I can't seem to figure out how if I say I have 5 or 50 in a range, how to output them out as such and also identify the non ranges in a list. Is there a VBA script that could help with this?
I have a large list of phone numbers in order. What I need to do is group consecutive ranges into a format of ##########-####. Everything is standard in column A with normal formatting with no fancy codes. Literally just the phone numbers.
Example:
5550000000
5550001111
5550001112
5550001114
5550001115
Output:
5550000000
5550001111-1112
5550001114-1115
I have thousands of phone numbers that I have to find those breaks. I often cheat a little by using a =IF(A2-A1=1),"RANGE","NOT RANGE." Or I might use MID to do the same with the last 4 values depending on my flavor of the day.
My experiments:
I've figured out how to identify if a number is consecutive and flag it as such.
I have used conditional formatting, copied into WORD and back into excel, then used a VBA formula to find where each break is in the list of TNs by identifying the color code.
Issue:
I can't seem to figure out how if I say I have 5 or 50 in a range, how to output them out as such and also identify the non ranges in a list. Is there a VBA script that could help with this?