Hi,
I'm looking for way to create a list of sequenced numbers from value of two cells.
I know I can drag manually from first cell till exact value, but it is not the best solution in my case.
For example
A1 cell contains number F1000 and B1 F2000
in column C i get a list from range A1 to B1
F1000
F1001
F1002
F1003
F1004
etc
F2000
Hi,
do you want the list in cell C1 eg. F1000,F1001...,F2000 ? OR values in C1,C2,...C2000 as F1000,F1000...,F2000 ?
I want to get list F1000, F1001... F2000.
does your text is going to vary in length or is it limited to only one character like in between A-Z
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hi ,
you can run the code in attached sheet by clicking the button, it populates a list in col c given the nos. would always be in 1000s.
Please Login or Register to view this content.
If this helped and you wish to say thanks, then Please click on the Star* icon below this post.
Bhuvi ! That's a good approach but how do you know text is always going to be one character in length or numbers are going to be only 4 character in length
Bhuvi, it is excactly as hemesh posted, first letter and number lenght is not always the same.
But we are on a right course, cause list is generated in a way I was looking for.
find attached !
Hope this helps
alternative approach could be using VBA
though not too good in vba and still learning hope this could help
this works same way the formula was working and excepts text in the left and all numbers in right.
adjusted code for first letter and varying number lengthOriginally Posted by Yahoho
Please Login or Register to view this content.
@Hemesh Thanks for sharing these two wonderful solutions (vba and nonVba)
@Bhuvi ! Glad I could contribute a little bit !
One more thing to notice is that if the text is only between the columns i.e. A till XFD then even a simpler approach can be taken with formula
keep the formula only for text means keep formula below A1 then in c1 use
=IFERROR($A$2&SMALL(ROW(INDIRECT($A$1&":"&$B$1)),ROW($A1)),"") hold control and shift then hit enter. then drag down
hemesh this works really fine! Just one question - Is it possible to get full lenght of numbers - 0001, 0002, 0003? Now VBA changes numbers F1, F2, F3.
Bhuvi in your new version it still doesn't change letter.
Try Below
this will have four 0's before startPlease Login or Register to view this content.
Last edited by hemesh; 02-27-2014 at 07:34 AM.
Thanks you very much Bhuvi and hemesh, my problem is solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks