concatenate problem

[tipoo]

Hi Guys,

I'm using =concatenate(A2,".",B2,".",C2) in Column D. But when I drag it
down from cell D2 down I get same values.

Column A Column B Column C Column D Vlaues in Column D
sould be
15007 5709 15007.5709.
15007.5709
18090 5015 300 15007.5709.
18090.5015.300

I want:
1 - When Column C is blank then do not use decimal at the end of the numbers
in column D;

2 - When I drag the formula down from cell D2 it change with the vlaue in A,
B and C column.

Will appreciate your feedback.

Thanks

 

[Jacob Skaria]

Try the below

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,".",B2,".",C2,"."),"..",".")&" ",". ",)

If this post helps click Yes

 

[Stefi]

In addition you should check your Calculations setting
(Tools>Options>Calculations tab), because your example suggests that it's set
to Manual. Set it to automatic!
Regards,
Stefi


„tipoo†ezt írta:

 

[tipoo]

You guys are amazing...! Thanks heaps it worked.

 

[tipoo]

Hi Stefi,

Amazing....! Your r spot on I did turn the auto cal off but how did u know
that I turned auto cal off?

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Stefi†ezt írta:

 

[Bernd P]

Hello,

=A2&"."&B2&REPT("."&C2,SIGN(LEN(C2)))

Copy down and ensure calculation is set to automatic as Stefi said.

Regards,
Bernd

 

[Stefi]

If you drag down a formula and auto calc is on then you should get the
desired values at once (except omitting redundant dots).
Stefi


„tipoo†ezt írta:

 

[David Biddulph]

Because you said that you got the same value and because you said that one
of the things you wanted was:
"When I drag the formula down from cell D2 it change with the vlaue in A, B
and C column."