Concatenate a range with values in between

Question

I work with ranged data and want to populate a cell that includes values at set increments between the min and maximum. For example, the minimum outer diameter is 1 1/16" and the maximum outer diameter is 1 11/32". I want to populate a third cell that includes each end plus all the values in between by the 32th of an inch: 1 1/16";1 3/32";1 1/8";1 5/32";1 3/16";1 7/32";1 1/4";1 9/32";1 5/16";1 11/32"

Can I do this in Excel 2007?

So far, I'm just copying and pasting from a master, semi-colon delimited list I have of all values in-between 1 1/64th and 2" and then going back and copying and replacing "1 " with "2 " and adding them to the string and so on.

I just tried Madball73's VBA code. I was able to get it to generate values iff the whole number value was the same for each limit and I removed the ". For example, I tried 1 1/64 to 1 1/4 (and changed the increment to 0.015625 since this one was to the 64th). The VBA code returned: ";1 1/64";1 1/32";1 3/64";1 1/16";1 5/64";1 3/32";1 7/64";1 1/8";1 9/64";1 5/32";1 11/64";1 3/16";1 13/64";1 7/32";1 15/64";1 1/4"

Getting closer!

Update: Okay, with quite a bit of tweaking and adding the ultimate " through concatenation and trimming the preceding ";, I'm able to get this to work.

Update 2: it doesn't seem to be working any more :(

Answer 1

Here's a custom VBA function for you. Use it by setting your formula: =IterateSizes(value(a1),value(a2))

Function IterateSizes(btmSize, topSize)
rtnValue = ""
incr = 0.03125

For i = btmSize To topSize Step incr
    fraction = Trim(Excel.WorksheetFunction.Text(i, "# ??/??"))
    rtnValue = rtnValue & """;" & fraction
Next i

IterateSizes = rtnValue
End Function

Note. It was quickly thrown together and works for the few inputs I gave it (which did not include the double-quotes). But, this is not fully tested does not handle invalid inputs, is not boundary tested, etc but is a starting point.

Answer 2

There's a simpler, non-VBA option here - Concatenate a cell range without vba in excel

There are the key steps. Follow that link for images, comments and other tricks.

Create delimiting character

It would be great if we could have a character separating values. Here is how:

• Select cell B2.
• Type =A1:A50&", " in formula bar.
• Edit: Depending on your regional settings, try this: =TRANSPOSE(A1:A50)&", "
• Press F9.
• Delete curly brackets in formula bar.
• Delete last delimiting character.
• Type =Concatenate( in front of all characters in formula bar.
• Type ) after last character in formula bar.
• Press Enter

---

I do this quite a lot and I find this slight modification to be more convenient (Office 2016):

• As above, enter something like =A1:A50&" " then press F9
• Delete the ={ from the start and the } from the end, then press enter
• Find and replace (ctrl-H) ";" with "&"
• Add = to the start of the cell