suryatry26 Posted November 27, 2012 Share Posted November 27, 2012 Hi I have doubt in Excel Concatenate Formula.Here i have pasted one reference image.As per that Image I am Concatenating 05888 & 0001 means output coming like 5888-1.But i want out Like 05888-0001.I changed that column format as a Text also.But same output is coming.Any option is there? If 0001 means 0001 only need to come. Quote Link to comment Share on other sites More sharing options...
MSasu Posted November 27, 2012 Share Posted November 27, 2012 (edited) If the type of data in first two columns is Text, then the formula below should work: =CONCATENATE(A1, "-", B1) Edited November 27, 2012 by MSasu Fixed the formula, "-" instead of "+". Quote Link to comment Share on other sites More sharing options...
suryatry26 Posted November 27, 2012 Author Share Posted November 27, 2012 No..Till Its coming like this..5888+1 Quote Link to comment Share on other sites More sharing options...
ReMark Posted November 27, 2012 Share Posted November 27, 2012 Is this result acceptable: 05888+0001 ? Or is a dash preferred over a plus sign? Quote Link to comment Share on other sites More sharing options...
Tyke Posted November 27, 2012 Share Posted November 27, 2012 As Mircea said format the cells as TEXT and then it works. But use semicolons not commas to separate the text elements. Quote Link to comment Share on other sites More sharing options...
ReMark Posted November 27, 2012 Share Posted November 27, 2012 No semi-colon's here. But, as indicate by the green triangles in the upper left hand corner, both cells are Text. Note that I change this "+" to this "-". Quote Link to comment Share on other sites More sharing options...
MSasu Posted November 27, 2012 Share Posted November 27, 2012 Or is a dash preferred over a plus sign? I'm afraid that it was my mistake when I wrote the formula. It should included a dash, as in OP's screen-shot. Sorry for inconvenience. Quote Link to comment Share on other sites More sharing options...
MSasu Posted November 27, 2012 Share Posted November 27, 2012 use semicolons not commas to separate the text elements. That is controled by what character you had set as List separator into Regional Settings of your system. Quote Link to comment Share on other sites More sharing options...
Tyke Posted November 27, 2012 Share Posted November 27, 2012 The difference is the language version. This is German and as the Germans use a comma as a decimal seperator and it could be that they use a semicolon to differentiate with the decimal seperator. I don't know. If you SUM some cells together they use a semicolon to seperate the list, what do they use in the English version ReMark? Sorry if I caused some confusion here. Quote Link to comment Share on other sites More sharing options...
ReMark Posted November 27, 2012 Share Posted November 27, 2012 As you can see I used a comma as my separator. Never thought to use a semicolon. Quote Link to comment Share on other sites More sharing options...
MSasu Posted November 27, 2012 Share Posted November 27, 2012 Gentlemen, please check post #8. Quote Link to comment Share on other sites More sharing options...
ReMark Posted November 27, 2012 Share Posted November 27, 2012 I hardly use Excel so the setting is what the setting is. Know what I mean? Quote Link to comment Share on other sites More sharing options...
Tyke Posted November 27, 2012 Share Posted November 27, 2012 Same here ReMark. My Excel set-up is OOTB and in Regional Settings my List Seperator is a ';' hence the difference. Thanks for the explanation Mircea. Let's see what the OP says when he comes back tomorrow. Quote Link to comment Share on other sites More sharing options...
suryatry26 Posted November 28, 2012 Author Share Posted November 28, 2012 i have tried with this all format .but till i am not get that value like 05888-0001 Quote Link to comment Share on other sites More sharing options...
MSasu Posted November 28, 2012 Share Posted November 28, 2012 The below formula will force the formatting of the two values being concatenated; the type of data should not make a difference. =CONCATENATE(TEXT(A1, "00000"), "-", TEXT(B1, "0000")) By the way, you may ask a moderator to move your thread into a public area for better visibility and therefore support. Quote Link to comment Share on other sites More sharing options...
suryatry26 Posted November 28, 2012 Author Share Posted November 28, 2012 MSasu now its working fine..thanks Quote Link to comment Share on other sites More sharing options...
ReMark Posted November 28, 2012 Share Posted November 28, 2012 I think you got your images labeled incorrectly in post #14. Quote Link to comment Share on other sites More sharing options...
MSasu Posted November 28, 2012 Share Posted November 28, 2012 @suryatry26: Glad to hear that is solved. You're welcome! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.