concatenate problem
 

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

concatenate problem
 

Try the below

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,".",B2,".",C 2,"."),"..",".")&" ",". ",)

If this post helps click Yes
---------------
Jacob Skaria


"tipoo" wrote:

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

concatenate problem
 

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


€žtipoo€� ezt Ã*rta:

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

concatenate problem
 

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

"Jacob Skaria" wrote:

Try the below

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,".",B2,".",C 2,"."),"..",".")&" ",". ",)

If this post helps click Yes
---------------
Jacob Skaria


"tipoo" wrote:

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

concatenate problem
 

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" wrote:

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


€žtipoo€� ezt Ã*rta:

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

concatenate problem
 

You are welcome! Thanks for the feedback!
Stefi

€žStefi€� ezt Ã*rta:

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


€žtipoo€� ezt Ã*rta:

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

concatenate problem
 

Hello,

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

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

Regards,
Bernd

concatenate problem
 

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:

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" wrote:

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


€žtipoo€� ezt Ã*rta:

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

concatenate problem
 

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."
--
David Biddulph


"tipoo" wrote in message
...
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" wrote:

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


"tipoo" ezt írta:

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