creating a series from alphanumeric data?

**rykey** · 09-11-2006, 11:51 AM

I've tried several things to accomplish this with no luck, but I'm sure it's not too tough to do:

On my worksheet, I am entering columns of cells with alphanumeric data. The data are actually thousands of reference numbers that need to go in chronological order, with only the numerals changing (e.g., ABC0001DEF, ABC0002DEF, ABC0003DEF..., with "ABC" and "DEF" always the same).

I can create a series just fine if I leave off the "DEF" part of the reference numbers (by selecting the first couple cells in the series and using the fill handle), but I can't get the same results with the numerals "nested" within the reference numbers (i.e., ABC####DEF).

It sure would save me a lot of typing to be able to create a series to fill in all my reference numbers! Any ideas?

**teelim** · 09-11-2006, 12:44 PM

I think this should work for you...

**rykey** · 09-11-2006, 12:51 PM

thanks! i knew it was simple for people here!

**rykey** · 09-11-2006, 01:10 PM

teelim,
i see the formula you sent me to concatenate the cells. did you set up the formulas for the D column (where you concatenated A,B, and C) using a formula as well (i.e., so you didn't have to type in the formula each time)?

**teelim** · 09-11-2006, 01:15 PM

sorry but I don't quite get your question...

anyway, the only "special" thing that I did was to enter the numbers in Col B as '0001 and so on. This is to ensure that it shows as ABC0001DEF instead of ABC1DEF. Thats all. CONCATENATE is just a regular formula to join together text from different cells. You can also use "&" instead

**rykey** · 09-11-2006, 01:25 PM

sorry if my question was confusing. let me try again:

i see how you used the "cocatenate" function in column D to join the data from A,B, and C. So, for example, in Row 1, your formula in D1 is =CONCATENATE(A1,B1,C1). So far so good!

My question is, did you do that by just typing "=CONCATENATE(A1,B1,C1)" in D1, then typing "=CONCATENATE(A2,B2,C2)" in D2, "=CONCATENATE(A3,B3,C3)" in D3, and so on for 28 rows?
*OR*
Did you use a formula or some other automated function to fill in the formulas in the D column (thus saving yourself all the typing of concatenate formulas)?

I ask this because typing in all those concatenate formulas would take more time in my case than just typing in all my ABC####DEF numbers by hand.

Did I explain that better?