Series including text possible?

S

StargateFan

Is there a better way to create a "series" that is based on characters
as well as digits than what I'm doing below?

I created 3 columns. I put the text in one, the digits in the second
that I could apply the series to, with the 3rd column holding a ":".
But when I paste into the text file, I have tabs separating the 3
columsn where I was hoping for no space.

So this here -

NAQAHDAH 01 :
NAQAHDAH 02 :
NAQAHDAH 03 :

should paste like this -
NAQAHD6H01:
NAQAHDAH02:
NAQAHDAH03:
NAQAHDAH04:
NAQAHDAH05:
NAQAHDAH06:
NAQAHDAH07:
NAQAHDAH08:
NAQAHDAH09

Is there perhaps a way to do this while keeping the 3 elements
together in each cell of the column? Or perhaps there's another way?

Thanks. :eek:D
 
N

Niek Otten

If you start in Row 1, use this formula:

="NAQAHDAH"&TEXT(ROW(),"0000")&":"

Fill down as far as you need

Use ROW()-1 if you start in Row 2, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Is there a better way to create a "series" that is based on characters
| as well as digits than what I'm doing below?
|
| I created 3 columns. I put the text in one, the digits in the second
| that I could apply the series to, with the 3rd column holding a ":".
| But when I paste into the text file, I have tabs separating the 3
| columsn where I was hoping for no space.
|
| So this here -
|
| NAQAHDAH 01 :
| NAQAHDAH 02 :
| NAQAHDAH 03 :
|
| should paste like this -
| NAQAHD6H01:
| NAQAHDAH02:
| NAQAHDAH03:
| NAQAHDAH04:
| NAQAHDAH05:
| NAQAHDAH06:
| NAQAHDAH07:
| NAQAHDAH08:
| NAQAHDAH09
|
| Is there perhaps a way to do this while keeping the 3 elements
| together in each cell of the column? Or perhaps there's another way?
|
| Thanks. :eek:D
|
 
N

Niek Otten

Make that

="NAQAHDAH"&TEXT(ROW(),"00")&":"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| If you start in Row 1, use this formula:
|
| ="NAQAHDAH"&TEXT(ROW(),"0000")&":"
|
| Fill down as far as you need
|
| Use ROW()-1 if you start in Row 2, etc
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| Is there a better way to create a "series" that is based on characters
|| as well as digits than what I'm doing below?
||
|| I created 3 columns. I put the text in one, the digits in the second
|| that I could apply the series to, with the 3rd column holding a ":".
|| But when I paste into the text file, I have tabs separating the 3
|| columsn where I was hoping for no space.
||
|| So this here -
||
|| NAQAHDAH 01 :
|| NAQAHDAH 02 :
|| NAQAHDAH 03 :
||
|| should paste like this -
|| NAQAHD6H01:
|| NAQAHDAH02:
|| NAQAHDAH03:
|| NAQAHDAH04:
|| NAQAHDAH05:
|| NAQAHDAH06:
|| NAQAHDAH07:
|| NAQAHDAH08:
|| NAQAHDAH09
||
|| Is there perhaps a way to do this while keeping the 3 elements
|| together in each cell of the column? Or perhaps there's another way?
||
|| Thanks. :eek:D
||
|
|
 
S

StargateFan

Make that

="NAQAHDAH"&TEXT(ROW(),"00")&":"
Click to expand...

That's just incredible. No matter how many new things I learn in XL
each day, they never cease to amaze me. This works beautifully.

Each time I'm adding multiple lines to the text file, I just need to
replace the preface text (in this case "naqadhdah") with whatever is
needed each time.

Also, by adding 2 spaces after the ":", I no longer have to fiddle but
can add the definition lines needed.

The formula for this particular entry looks like this now:

="NAQAHDAH"&TEXT(ROW(),"00")&": "

Thanks. :eek:D
 
Top