Hello!

I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001, 002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)

1 - How is possible to do this, please?
2 - The result is possible to format as number, please?

Thanks in advence.
an

Hi an

try
=VALUE(A1&TEXT(B1,"000")&C1&D1)

Regards
JulieD

"an" wrote in message
...
Hello!

I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001, 002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)

1 - How is possible to do this, please?
2 - The result is possible to format as number, please?

Thanks in advence.
an

Try

=--(A1&TEXT(B1,"000")&C1&D1)



"an" wrote in message
...
Hello!

I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001, 002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)

1 - How is possible to do this, please?
2 - The result is possible to format as number, please?

Thanks in advence.
an

One way

=A2&TEXT(B2,"000")&C2&D2


Regards,

Peo Sjoblom

"an" wrote:

Hello!

I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001, 002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)

1 - How is possible to do this, please?
2 - The result is possible to format as number, please?

Thanks in advence.
an

an wrote...
I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001, 002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)
....

An alternative,

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

Ok!
Debtor to all.
All works fine.
an

-----Original Message-----
Hello!

I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001,
002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)

1 - How is possible to do this, please?
2 - The result is possible to format as number, please?

Thanks in advence.
an




.

Thanks too.
But Return #VALUE (?)
an

-----Original Message-----
an wrote...
I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001,
002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)
....

An alternative,

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

.

You can try adding -- to ensure a2:d2 are counted as numbers

=SUMPRODUCT(--A2:D2,{1000000,100,10,1})


"an" wrote in message
...
Thanks too.
But Return #VALUE (?)
an

-----Original Message-----
an wrote...
I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001,
002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)
....

An alternative,

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

.

Hmm, well, I guess it wouldn't hurt to add -- before the range, but in
testing it doesn't seem to make a difference. Not sure why you would get
that error message.



"Dave R." wrote in message
...
You can try adding -- to ensure a2:d2 are counted as numbers

=SUMPRODUCT(--A2:D2,{1000000,100,10,1})


"an" wrote in message
...
Thanks too.
But Return #VALUE (?)
an

-----Original Message-----
an wrote...
I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001,
002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)
....

An alternative,

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

.

Thanks for your reply.

Perhaps because in Col D I have formatted with Custom to
have 3 digits (?)
an
-----Original Message-----
Hmm, well, I guess it wouldn't hurt to add -- before the
range, but in
testing it doesn't seem to make a difference. Not sure
why you would get
that error message.



"Dave R." wrote in message
...
You can try adding -- to ensure a2:d2 are counted as
numbers

=SUMPRODUCT(--A2:D2,{1000000,100,10,1})


"an" wrote in
message
...
Thanks too.
But Return #VALUE (?)
an

-----Original Message-----
an wrote...
I have next cells:
A B C D
101 1 3

With =A1 & C1 & D1, I obtained 10113.
However, I need to intercalate one string type 001,
002...
between cells.

Example:
A B C D
101 001 1 3
(Where 001 is formated with Custom to have 3 digits.
I would like to obtain 10100113.)
....

An alternative,

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

.





.

an wrote...
....
Perhaps because in Col D I have formatted with Custom to
have 3 digits (?)
an
....
"Dave R." wrote in message
You can try adding -- to ensure a2:d2 are counted as
numbers

=SUMPRODUCT(--A2:D2,{1000000,100,10,1})

"an" wrote in
Thanks too.
But Return #VALUE (?)
....
=SUMPRODUCT(A2:D2,{1000000,100,10,1})

The only way the exact formula

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

could return #VALUE! would be A2:D2 containing cells that evaluate to
#VALUE!. With any combination of blank cells and cells containing
numbers, text or boolean values in A2:D2, the exact formula above will
ALWAYS return a number or #NUM! in case of overflow or underflow.

SUMPRODUCT returns #VALUE! when its arguments aren't all the same size
and shape. If you're not using standard US regional settings, then the
array constant could be mangled on entry, but that would cause a syntax
error, so Excel would display an error dialog when you try to enter the
formula. Does it?

What *EXACTLY* is the formula you've entered? Don't translate. Select
the cell containing the formula, press in sequence [F2],
[Shift]+[Home], [Ctrl]+C, [Esc]. Then paste into your newsgroup
response. Also, what *EXACTLY* are the values of the cells in the first
argument?

an wrote...
....
"an" wrote in
But Return #VALUE (?)
....
=SUMPRODUCT(A2:D2,{1000000,100,10,1})

Google seems to have eaten my last reply.

In a nutshell, SUMPRODUCT will only return #VALUE! in the exact formula
above if one of the cells in A2:D2 evaluates to #VALUE!. If all cells
in A2:D2 are blank or contain numbers, text or booleans, then the only
possible error return value from this exact formula would be #NUM! in
case of overflow.

Assuming no error values in its arguments, SUMPRODUCT would only return
#VALUE! if its arguments weren't the same size or shape. That's not the
case in the exact formula above under US regional settings. If the OP
(an) is running under different regional settings, the array constant
(which should have been {100000,100,10,1}) may need to use something
other than comma to separate horizontal array entries. In that case,
Excel should have treated the formula above as having syntax errors and
displayed an error dialog. Did it?

"Harlan Grove" wrote in message
oups.com...
an wrote...
...
"an" wrote in
But Return #VALUE (?)
...
=SUMPRODUCT(A2:D2,{1000000,100,10,1})

Google seems to have eaten my last reply.



It made it to the newsgroup:



Harlan Grove" wrote in message
oups.com...


The only way the exact formula

=SUMPRODUCT(A2:D2,{1000000,100,10,1})

could return #VALUE! would be A2:D2 containing cells that evaluate to
#VALUE!. With any combination of blank cells and cells containing
numbers, text or boolean values in A2:D2, the exact formula above will
ALWAYS return a number or #NUM! in case of overflow or underflow.

SUMPRODUCT returns #VALUE! when its arguments aren't all the same size
and shape. If you're not using standard US regional settings, then the
array constant could be mangled on entry, but that would cause a syntax
error, so Excel would display an error dialog when you try to enter the
formula. Does it?

What *EXACTLY* is the formula you've entered? Don't translate. Select
the cell containing the formula, press in sequence [F2],
[Shift]+[Home], [Ctrl]+C, [Esc]. Then paste into your newsgroup
response. Also, what *EXACTLY* are the values of the cells in the first
argument?