Creating sequential text string of fixed length

[dhstein]

Hi - I need a formula that will generate text strings in rows like

row 1 0001
row 2 0002
row 3 0003
..
..
..
row 9999 9999

I want the leading zeros and I can't figure out what value to use in the
Text formula to get that to work. Thanks for your help.

 

[BDT]

Try:

=Text(row(),"0000")

That didn't work for me, but this did

Put Row 1 0001 in Col B
Put 0001 in Col C
Put function =RIGHT(B2,4) in Col D
Put function =CONCATENATE(C2," ",D2) in Col E
Copy down 9999 rows

Use Copy & Paste Special/Values to put Col E info into an empty column and
delete Cols B thru E.

It's not very elegant, but it does the job quick and EZ.

good luck

 

[Max]

=Text(row(),"0000")

That didn't work for me

I'm quizzical. Fred's suggestion works ok for me, and it should for you,
too, as long as its placed in any cell in row1, then copied down from there
(since ROW() is sensitive to the row it is placed in).

What exactly did the expression return for you?

For increased flexibility to start with 0001 in any cell, just apply a small
tweak
In any starting cell, say, in B3: =TEXT(ROWS($1:1),"0000")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---

 

[BDT]

Here is a better solution than the one I suggested earlier.

Try =CONCATENATE("Row ", ROW(), " ", TEXT(ROW(),"0000"))

then you can copy the row and Paste Special/value over itself to remove
all of the functions so it doesn't stay linked to the row number. It's just
text at that point.

hope this helps

 

[BDT]

Hi Max and Fred. I was taking the question too literally. I was trying to
generate the whole thing .... Row 5 0005 , not just the 0005 .

Sorry if I confused the issue. Fred's earlier solution gave me something to
use to build a function for the whole thing that I posted a moment ago.

cheers,

 

[dhstein]

Here is a better solution than the one I suggested earlier.

Try =CONCATENATE("Row ", ROW(), " ", TEXT(ROW(),"0000"))

then you can copy the row and Paste Special/value over itself to remove
all of the functions so it doesn't stay linked to the row number. It's just
text at that point.

hope this helps

Thanks for all the responses - the documentation on TEXT is a little sparse
and hard to find the "0000" which was what I needed.

 

[Max]

Please take a moment to press the YES button (like the one below) in ALL
responses which helped to answer your query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik