Creating sequential text string of fixed length
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. |
Creating sequential text string of fixed length
Try:
=Text(row(),"0000") Regards, Fred. "dhstein" wrote in message ... 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. |
Creating sequential text string of fixed length
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 |
Creating sequential text string of fixed length
=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 --- |
Creating sequential text string of fixed length
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 "dhstein" wrote: 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. |
Creating sequential text string of fixed length
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, "Max" wrote: =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 --- |
Creating sequential text string of fixed length
"BDT" wrote: 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 "dhstein" wrote: 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. Thanks for all the responses - the documentation on TEXT is a little sparse and hard to find the "0000" which was what I needed. |
Creating sequential text string of fixed length
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 --- "dhstein" wrote: Thanks for all the responses - the documentation on TEXT is a little sparse and hard to find the "0000" which was what I needed. |
All times are GMT +1. The time now is 11:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com