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

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

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

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

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