Hi I want to know how to combine numbers together in the same cell. For example, lets say I have the number 0,1,2,3,4,5,6,7 in the cell A1,A2,A3,A4,A5,A6,A7,A8 respectively. I want to add up the number so that it gives me 0,1234567 in cell A9. I managed to combine them with concatenate but its in text form and I cant put the comma after the 0. Thanks for the help.
Last edited by cyber916; 09-27-2015 at 03:48 PM.
edit:- title change request removed.
Why comma after the zero, do you use comma instead of period / dot as a decimal separator in Canada?
See ifworksFormula:
Please Login or Register to view this content.
Last edited by jason.b75; 09-27-2015 at 03:03 PM.
ya actually when i said comma i meant a dot. concatenate seems to put my numbers into a text form but i don't want that.
That's standard for excel, any of these will convert the text back to a number.
=--CONCATENATE(A1,".",B1)
=CONCATENATE(A1,".",B1)+0
=VALUE(CONCATENATE(A1,".",B1)
=--(A1&"."&B1)
=(A1&"."&B1)+0
=VALUE(A1&"."&B1)
You could also use *1 instead of +0 with either of the methods above, that gives you a total of 8 ways to do what you need. There are more if you get creative.
Anything that cannot be converted to a number will return #VALUE!
Any time you use Concat (or join cells), the answer is always text, even if the cells contain values.
Try this...
=--(A1&"."&A2&A3&A4&A5&A6&A7&A8)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
It worked! Thanks for the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks