Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > concatenate problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,646
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,646
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 806
Default concatenate problem
Hello,

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

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

Regards,
Bernd
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,646
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,651
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate problem harwookf Excel Discussion (Misc queries) 7 July 13th 09 04:46 PM
Concatenate Problem John Calder New Users to Excel 7 April 2nd 08 01:11 AM
Odd Concatenate problem. Paul Excel Worksheet Functions 9 December 13th 07 04:45 AM
Concatenate Problem John Calder New Users to Excel 3 November 13th 06 10:39 PM
concatenate problem joe peters Excel Worksheet Functions 9 May 29th 05 06:34 AM


All times are GMT +1. The time now is 06:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement