I would like to know the VBA code or the formula to convert ranges in to a simplified format.

For example, the first ranges ends with "0" and the end ranges end with "9". Therefore, you have to look at both ranges to determine the number and decide the lengthe of the number and then extend the names.

I tried text to columms and devided into 4 digits each and filtered by doing true and false. I would like to know if there is a better way to do this.

Thank you.

Book2 | ||||||
---|---|---|---|---|---|---|

A | B | C | D | |||

1 | NAMES | FirstRange | EndRange | Rate | ||

2 | A | 79516000 | 79516999 | 0.0759 | ||

3 | B | 79510000 | 79519999 | 0.0759 | ||

4 | C | 79518100 | 79518149 | 0.0759 | ||

5 | D | 79518150 | 79518999 | 0.0759 | ||

6 | ||||||

7 | NAMES | FirstRange | Rate | |||

8 | A | 79516 | 0.0759 | |||

9 | B | 7951 | 0.0759 | |||

10 | C | 7951810 | 0.0759 | |||

11 | C | 7951811 | 0.0759 | |||

12 | C | 7951812 | 0.0759 | |||

13 | C | 7951813 | 0.0759 | |||

14 | C | 7951814 | 0.0759 | |||

15 | D | 7951815 | 0.0759 | |||

16 | D | 7951816 | 0.0759 | |||

17 | D | 7951817 | 0.0759 | |||

18 | D | 7951818 | 0.0759 | |||

19 | D | 7951819 | 0.0759 | |||

Sheet1 |