Range strings

[daily106]

hello! how you all doing?

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

 

[Norie]

So what you want to do is this?

If the First Range ends with 00 and the End Range with 99 only place the value from the First Range, excluding the 0's in B.

If that condition is not satisfied then fill down column B with the values from the First Range to the End Range incrementing by one.

 

[daily106]

Thank you Norie. While answering your question, I tried different approach and it kinda works.

More examples of "begin" to "End"
1230000 to 1239999 is 123
1230000 to 1249999 are from 1230 through 1249 increment by one
1234900 to 1234999 are from 123490 through 123499 increment by one
1234000 to 1234199 are 12340 and 12341

The problem is the 0s from "begin ranges" does not have to end with the same count of 9s from the "end ranges"..like the 4th example I provided.


So, I took the different approach to solve this problem...

"End Range" - "Bengin Range"

If the value comes up as all 9s, only place the value from the first range(or the end range) excluding the count of 0s nor 9s.

Example
1230000 to 1239999
1239999 minus 1230000 is 9999(4digits) so place the value without the right 4
Therefore 123

1233000 to 1233999 would be 1233


If the vale comes up as any other numbers follow by 9s, add the that number of rows and increment 1 by the "begin" without the 0s.

Example
1230000 to 1249999

1249999 minus 1230000 is 19999 so insert one row and add 124


Well I think I may have confused you even more...but I think I can do this by subtraction of each ranges.



Thank you for your help!!!!!!!