• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

merge two cells and keep the commas that exist in cell 1

C

chandoorich

New Member
Hello


A1: 614,735

B1: 899


I would like C1 to show: 614,735,899


I'm new to excel and this forum, please can anyone advise me of what formula or combination of formula and settings would get this to work.

I'm using Excel 2013.


Thnak You
 
Hi ,


It all depends on what you want the result to be , numeric or text.


If it is numeric , then what you need in C1 is a value which is A1*1000 + B1 , which when formatted will show the commas correctly.


If it is text , then what you need in C1 is a string which is a concatenation of A1 and B1 , through a formula such as =A1&","&B1. This will be OK if A1 is a string with the comma as part of the string ; if A1 is actually a number , and the comma is only from cell formatting , then you need to use =TEXT(A1,"#,##0")&","&B1.


Narayan
 
I agree with Narayan, I think more info is needed about whether these are numbers, text, multiple cells (more than 2?), variable length, etc.


I'll take the guess that they are both numbers, but variable length. In which case, formula is:

=A1*10^LEN(A2)+A2

and format cell as a number with 0 decimals.
 
Thanks for your help,


tried both ways suggested above and neither worked.

I'm not sure how to identify whether the cells are formatted as numbers or text.

I'll have a look through the forum and try find a way to identify them.

I was going to upload a sheet as an example but can't see any easy way to attach one.


Thanks
 
Hi ,


A statement like neither worked neither helps you nor helps us !


When troubleshooting a problem , give till it hurts ; give as much information and detail as possible ; just saying something has not worked may give you satisfaction , but it in no way brings us closer to a solution.


Can you tell us for the three formulae that were given , what the results in C1 were ?


Narayan
 
Thought I'd mention how important the commas are they need to be kept in the cells and without spaces, exactly like IN my initial post.


I think from what I can see, both columns are set to number, decimal places 0 with the use 1000 separator ticked.


They columns are id's are actually X2, THEN AA2 AND RESULT IN AB2


Hope this helps.
 
Narayan


mmm Yeah, so what you said in your first reply has worked the second time.


Possibly I cut and pasted something incorrectly, though I have been getting to grips with formatting cells (they now both match as per post no.4: set to number, decimal places 0, with the use 1000 separator ticked) I've now definitely got the comma separation I needed. Thank you very much :-)


Would this work if either cells had a differing amount of numbers in?

Say for instance cell A: has 765,976,345,234 and cell B: has 543,234,123 or any variation?


Why ask?, I'll try and upload an answer myself.
 
Hi ,


If we modify the earlier formulae for the revised column IDs , they become :


1. =X2&","&AA2


2. =TEXT(X2,"#,##0")&","&AA2


3. =X2*10^LEN(AA2)+AA2


Post here the result for each of the above.


Narayan
 
Hi ,


You need to be aware that beyond a certain number of places , Excel will not represent numbers the way you would expect ; beyond 15 digits , all other digits will be zeros.


Narayan
 
When troubleshooting a problem , give till it hurts

...I love that line. To quote the classic Aussie movie 'The Castle', that is going straight to the pool room.


Works the other way too...When answering a problem , give till it hurts. I know you certainly do, Ninjas.
 
You must log in or register to reply here.
Back
Top