Range strings

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
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
ABCD
1NAMESFirstRangeEndRangeRate
2A79516000795169990.0759
3B79510000795199990.0759
4C79518100795181490.0759
5D79518150795189990.0759
6
7NAMESFirstRangeRate
8A795160.0759
9B79510.0759
10C79518100.0759
11C79518110.0759
12C79518120.0759
13C79518130.0759
14C79518140.0759
15D79518150.0759
16D79518160.0759
17D79518170.0759
18D79518180.0759
19D79518190.0759
Sheet1
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,929
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 20, 2004
Messages
158
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!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,385
Messages
5,547,650
Members
410,805
Latest member
Ginoji
Top