Excel formula? 1

[MinnKota]

We have a spreadsheet that has a StartingNumber column and an EndingNumber column. Is there a formula that will return all of the numbers in between the two?

 

[MinnKota]

Thanks for being so patient,

I was given an Excel spreadsheet that has two Columns which represent the beginning of range of serial numbers and the end of the range of serial numbers. i.e. (1 and 10) which represents 1,2,3,4,5,6,7,8,9,10 BUT I found out today that there is a third component...a prefix so if the prefix is "MKAE" then (1 and 10) represents MKAE1, MKAE2, MKAE3...and so on. Your function works great except when there are serial numbers that are proceeded by a few zeros, such as MKAE0004532.

The users will be using a scanning want to find (ctrl + F) a certain serial number. That is why I needed every serial number to be noted in a column.

 

[SkipVought]

I still do not know what you are going to do with the string

"MKAE1, MKAE2, MKAE3"

???



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

I have populated a whole row in Excel with these strings. The user will then use the build in find in Excel to "find" the specific serial number, which will be inputed with a barcode reader. If they were just typing the numbers in, then they could simply look for part of the number (exlude the zeros) but since they are scanning the number in, I need the zeros.

 

[SkipVought]

Would it not be better to have each SerialNumbers in an individual cell?

Then the AutoFilter could be used to POINT 'n' CLICK rather than typing data into a FIND Textbox???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

The function is easily modified to include a PREFIX...

Function NumString(rStart As Range, rEnd As Range, rPrefix As Range)
   Dim i As Integer
   Const COMMA = ","
   For i = rStart To rEnd
      NumString = NumString & rPrefix & i & COMMA
   Next
   NumString = Left(NumString, Len(NumString) - 1)
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

I have already made the function modifcation to add the prefix.

But instead of returning "MKAE000345" it returns "MKAE345" which will not work for my application since the barcode is actually "MKAE000345."

The only solution I have thought of it checking the length of "000345" and comparing it to itself once 1 has been added to it ("346") Then take the difference and add that number of zeros to the beginning.

It seems that the whole crux of my problem is the adding of 1. Stepping through my code, it appears that the zeros are removed at this point.

 

[SkipVought]

Then there's ONE more argument to pass -- the LENGTH OF THE SERIAL NUMBER

Function NumString(rStart As Range, rEnd As Range, rPrefix As Range, rLen As Range)
   Dim i As Integer, p As String
   Const COMMA = ","
   For i = rStart To rEnd
      p = rPrefix & Left("000000000000000", rLen - Len(rPrefix & i))
      NumString = NumString & p & i & COMMA
   Next
   NumString = Left(NumString, Len(NumString) - 1)
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

Skip,

Sorry for all the run around. This last bit of code is exactly what I needed! Many thanks,