A & B & C & D

[an]

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

 

[JulieD]

Hi an

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

Regards
JulieD

 

[Dave R.]

Try

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

 

[Peo Sjoblom]

One way

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


Regards,

Peo Sjoblom

 

[Harlan Grove]

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(A22,{1000000,100,10,1})

 

[an]

Ok!
Debtor to all.
All works fine.
an

 

[an]

Thanks too.
But Return #VALUE (?)
an

-----Original Message-----
an wrote...
....

An alternative,

=SUMPRODUCT(A22,{1000000,100,10,1})

.

 

[Dave R.]

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

=SUMPRODUCT(--A22,{1000000,100,10,1})

 

[Dave R.]

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.

 

[an]

Thanks for your reply.

Perhaps because in Col D I have formatted with Custom to
have 3 digits (?)
an

 

[Dave R.]

I tried that too, and it worked fine the original way. Maybe Harlan knows,
but it does sound like the other formulas worked for you.

 

[Harlan Grove]

an wrote...
....

Perhaps because in Col D I have formatted with Custom to
have 3 digits (?)
an ....

The only way the exact formula

=SUMPRODUCT(A22,{1000000,100,10,1})

could return #VALUE! would be A22 containing cells that evaluate to
#VALUE!. With any combination of blank cells and cells containing
numbers, text or boolean values in A22, 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?

 

[Harlan Grove]

an wrote...
....
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 A22 evaluates to #VALUE!. If all cells
in A22 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?

 

[Dave R.]

an wrote...
...

Google seems to have eaten my last reply.

It made it to the newsgroup:

The only way the exact formula

=SUMPRODUCT(A22,{1000000,100,10,1})

could return #VALUE! would be A22 containing cells that evaluate to
#VALUE!. With any combination of blank cells and cells containing
numbers, text or boolean values in A22, 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?