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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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!!!!!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,550
Members
431,885
Latest member
Rsdg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top