Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to column fixed length | Excel Discussion (Misc queries) | |||
Fixed Length, Text Formatted | Excel Discussion (Misc queries) | |||
creating numbers to a fixed length string... | Excel Worksheet Functions | |||
Fixed length text file | Excel Discussion (Misc queries) | |||
fixed string length,even other record is copied | Excel Discussion (Misc queries) |