| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add zeros to fill in and
You could use:
=G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7&" "&F5 or =G5&" "&A7&" "&B7&" "&text(g7,rept("0",10)&F7&rept(" ",23)&F5 Ps. You don't need &""& Using =rept() makes it easier to see things (for me anyway). LG wrote: Below is the information I have cell G5 = $PAAY3 cell A7 = ABC cell B7 = 20090810058 cell G7= 0000000000 cell F7= 16833 cell F5= LG The top is the formula I have complete the bottom is what I need it to show as =G5&" "&A7&" "&B7&" "&G7&""&F7&" "&F5 $PAAY3 ABC 20090810058 0000016833 LG I need to be able to get the zeros in G7 to fill in the #s from F7 in the final product while deleting the zeros from G7 that is used can anyone help. -- Dave Peterson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
add zeros to fill in and
"Dave Peterson" wrote:
=G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7 &" "&F5 I think that should be: =G5&" "&A7&" "&B7&" "&text(F7,"0000000000") &" "&F5 Note that LG wants the result to be: $PAAY3 ABC 20090810058 0000016833 LG where 16833 comes from F7, not G7. Likewise: =G5&" "&A7&" "&B7&" "&text(F7,rept("0",10)) &rept(" ",23)&F5 ----- original message ----- "Dave Peterson" wrote in message ... You could use: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7&" "&F5 or =G5&" "&A7&" "&B7&" "&text(g7,rept("0",10)&F7&rept(" ",23)&F5 Ps. You don't need &""& Using =rept() makes it easier to see things (for me anyway). LG wrote: Below is the information I have cell G5 = $PAAY3 cell A7 = ABC cell B7 = 20090810058 cell G7= 0000000000 cell F7= 16833 cell F5= LG The top is the formula I have complete the bottom is what I need it to show as =G5&" "&A7&" "&B7&" "&G7&""&F7&" "&F5 $PAAY3 ABC 20090810058 0000016833 LG I need to be able to get the zeros in G7 to fill in the #s from F7 in the final product while deleting the zeros from G7 that is used can anyone help. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
add zeros to fill in and
Actually, as convoluted as the description was, I think the OP is looking
for this... =G5&" "&A7&" "&B7&" "&TEXT(F7,G7)&" "&F5 where I'm assuming G7 is formatted as Text in order to be able to hold all those zeroes. What I think he wants is to use G7 as a pattern and replace the zeroes at the end of it with the number in F7 (with each digit in F7 replacing a zero in G7's pattern of zeroes). -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "Dave Peterson" wrote: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7 &" "&F5 I think that should be: =G5&" "&A7&" "&B7&" "&text(F7,"0000000000") &" "&F5 Note that LG wants the result to be: $PAAY3 ABC 20090810058 0000016833 LG where 16833 comes from F7, not G7. Likewise: =G5&" "&A7&" "&B7&" "&text(F7,rept("0",10)) &rept(" ",23)&F5 ----- original message ----- "Dave Peterson" wrote in message ... You could use: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7&" "&F5 or =G5&" "&A7&" "&B7&" "&text(g7,rept("0",10)&F7&rept(" ",23)&F5 Ps. You don't need &""& Using =rept() makes it easier to see things (for me anyway). LG wrote: Below is the information I have cell G5 = $PAAY3 cell A7 = ABC cell B7 = 20090810058 cell G7= 0000000000 cell F7= 16833 cell F5= LG The top is the formula I have complete the bottom is what I need it to show as =G5&" "&A7&" "&B7&" "&G7&""&F7&" "&F5 $PAAY3 ABC 20090810058 0000016833 LG I need to be able to get the zeros in G7 to fill in the #s from F7 in the final product while deleting the zeros from G7 that is used can anyone help. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
add zeros to fill in and
"Rick Rothstein" wrote:
Actually, as convoluted as the description was, I think the OP is looking for this... =G5&" "&A7&" "&B7&" "&TEXT(F7,G7)&" "&F5 I was trying to show LG that G7 probably was not necessary, especially if LG only wanted zero fill-in. (Hint: see the article subject.) "Everything should be made as simple as possible, but not simpler" -- Einstein. Even if LG wanted the fill-in to be a variable, I would opt for putting a single character into G7 and using REPT. First, counting long strings of characters is error-prone and difficult to maintain. Second, even the length of the filled-in F7 could be a variable (H7). For example: REPT(G7,MAX(0,H7-LEN(F7))). Obviously there is no single right answer. It's a judgment call. LG now has at least 3 good approaches to choose from. ----- original message ----- "Rick Rothstein" wrote in message ... Actually, as convoluted as the description was, I think the OP is looking for this... =G5&" "&A7&" "&B7&" "&TEXT(F7,G7)&" "&F5 where I'm assuming G7 is formatted as Text in order to be able to hold all those zeroes. What I think he wants is to use G7 as a pattern and replace the zeroes at the end of it with the number in F7 (with each digit in F7 replacing a zero in G7's pattern of zeroes). -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "Dave Peterson" wrote: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7 &" "&F5 I think that should be: =G5&" "&A7&" "&B7&" "&text(F7,"0000000000") &" "&F5 Note that LG wants the result to be: $PAAY3 ABC 20090810058 0000016833 LG where 16833 comes from F7, not G7. Likewise: =G5&" "&A7&" "&B7&" "&text(F7,rept("0",10)) &rept(" ",23)&F5 ----- original message ----- "Dave Peterson" wrote in message ... You could use: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7&" "&F5 or =G5&" "&A7&" "&B7&" "&text(g7,rept("0",10)&F7&rept(" ",23)&F5 Ps. You don't need &""& Using =rept() makes it easier to see things (for me anyway). LG wrote: Below is the information I have cell G5 = $PAAY3 cell A7 = ABC cell B7 = 20090810058 cell G7= 0000000000 cell F7= 16833 cell F5= LG The top is the formula I have complete the bottom is what I need it to show as =G5&" "&A7&" "&B7&" "&G7&""&F7&" "&F5 $PAAY3 ABC 20090810058 0000016833 LG I need to be able to get the zeros in G7 to fill in the #s from F7 in the final product while deleting the zeros from G7 that is used can anyone help. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
add zeros to fill in and
While I have no good reason for having done so (just a gut feeling), I
assumed the data was being given to the OP as opposed to him/her typing it in directly... if that turns out to be the case, then he/she would not have any control of what goes in G7. As you said, though, at least he/she now has some alternatives to choose from. -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "Rick Rothstein" wrote: Actually, as convoluted as the description was, I think the OP is looking for this... =G5&" "&A7&" "&B7&" "&TEXT(F7,G7)&" "&F5 I was trying to show LG that G7 probably was not necessary, especially if LG only wanted zero fill-in. (Hint: see the article subject.) "Everything should be made as simple as possible, but not simpler" -- Einstein. Even if LG wanted the fill-in to be a variable, I would opt for putting a single character into G7 and using REPT. First, counting long strings of characters is error-prone and difficult to maintain. Second, even the length of the filled-in F7 could be a variable (H7). For example: REPT(G7,MAX(0,H7-LEN(F7))). Obviously there is no single right answer. It's a judgment call. LG now has at least 3 good approaches to choose from. ----- original message ----- "Rick Rothstein" wrote in message ... Actually, as convoluted as the description was, I think the OP is looking for this... =G5&" "&A7&" "&B7&" "&TEXT(F7,G7)&" "&F5 where I'm assuming G7 is formatted as Text in order to be able to hold all those zeroes. What I think he wants is to use G7 as a pattern and replace the zeroes at the end of it with the number in F7 (with each digit in F7 replacing a zero in G7's pattern of zeroes). -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "Dave Peterson" wrote: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7 &" "&F5 I think that should be: =G5&" "&A7&" "&B7&" "&text(F7,"0000000000") &" "&F5 Note that LG wants the result to be: $PAAY3 ABC 20090810058 0000016833 LG where 16833 comes from F7, not G7. Likewise: =G5&" "&A7&" "&B7&" "&text(F7,rept("0",10)) &rept(" ",23)&F5 ----- original message ----- "Dave Peterson" wrote in message ... You could use: =G5&" "&A7&" "&B7&" "&text(g7,"0000000000")&F7&" "&F5 or =G5&" "&A7&" "&B7&" "&text(g7,rept("0",10)&F7&rept(" ",23)&F5 Ps. You don't need &""& Using =rept() makes it easier to see things (for me anyway). LG wrote: Below is the information I have cell G5 = $PAAY3 cell A7 = ABC cell B7 = 20090810058 cell G7= 0000000000 cell F7= 16833 cell F5= LG The top is the formula I have complete the bottom is what I need it to show as =G5&" "&A7&" "&B7&" "&G7&""&F7&" "&F5 $PAAY3 ABC 20090810058 0000016833 LG I need to be able to get the zeros in G7 to fill in the #s from F7 in the final product while deleting the zeros from G7 that is used can anyone help. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| when i megre cells that begin with zeros , I lose the zeros | Excel Discussion (Misc queries) | |||
| Essbase: Text zeros to number zeros | Excel Discussion (Misc queries) | |||
| Charting zeros as non zeros | Charts and Charting in Excel | |||
| How do I make a number have leading zeros to fill width of cell? | Excel Discussion (Misc queries) | |||
| save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
Hybrid Mode
