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})

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})

.





.

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




.