I've got a big data set from a measurement, only the value 20,352838 is in the format 20352838. So i need to add a comma after 2 digits. I can't just devide by 100000 because some numbers have a zero as last decimal. Does anybody know the format I should enter or does anybody have a different solution? Thanks for the help!
You can use the variables LEFT and RIGHT to select certain characters from a cell, and then the '&' symbol to concatenate them.
In this example, you would concatenate the left 2 digits, then a comma, then the remaining digits. The number of remaining digits can be found using the LEN variable.
Eg, where the data is in B10: =LEFT(B10,2)&","&RIGHT(B10,LEN(B10)-2)
This says "Take the first (leftmost) 2 digits from B10, then add a comma, then take a number of digits from the end (rightmost) of B10 equal to the length of B10 minus 2 (the 2 you've already put in there.)
Hope this makes sense.
Last edited by Bglamb; 09-15-2016 at 08:37 AM.
Here's another one...
=LEFT(A1,2)&","&MID(A1,3,20)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Both those will leave you with text that looks like a number. That's fine, unless you want to do something else with the number.
So. Use this as a custom formatting:
##\,#####0
Last edited by Glenn Kennedy; 09-15-2016 at 10:43 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
and one more
Formula:
Please Login or Register to view this content.
v A B 1 20352838 20,352838
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks! This does the trick
Ummm. You have 4 answers... As a matter of interest... which one(s) did the trick??
Oh it doesn't work when the number is 1 character shorter because the last decimal is a zero. I'm trying to solve this now by using =IF(LEN(D24)=7;D24*10;D24) but this seems a bit far fetched.
Which one doesn't work?
Sorry I'm new to this forum. The one from you Glennbut there was a problem as you can read above.
So far the answers that use the replace,left,right commands give errors...
Can you give an example of a number where "the last decimal is a zero"
Do you mean that you want the comma in a different place? Because all of these should work even when the numbers are different lengths or have 0s in them.
this is an example of the data set:Originally Posted by Bglamb
20.357.725
20.360.186
20.365.374
20.366.169
2.036.298
20.359.388
20.353.537
20.345.022
2.033.518
20.324.474
when i use the format Glenn gave me i get:
20,357725
20,360186
20,365374
20,366169
2,036298
20,359388
20,353537
20,345022
2,033518
20,324474
You can see the ones with length 7 give the wrong number.
The software that produced this data gives the data with 6 decimals, but if the last decimal is a zero it doesn't 'write' a zero, so the length is different.
So. Is this your REAL question??? "I have a set of 7 digit numbers and 8 digit numbers. I want a comma separator after the second digit."
Please confirm or correct me.
Or do you want the seven digit "numbers" padded out to 8 again with a 0?
Here are both possible options. If this is NOT what you want, please attach a sample workbook showing what you are starting with and what you want to finish with.
If you need to attach a sample, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.
Try this in custom format :
Please Login or Register to view this content.
Thanks! this works fine. Sorry if my question was unclear
As a matter of interest (again!!) which was the option that you had wanted?
It looks like you have to use semi-colons rather than commas as separators.
=LEFT(A1;2)&","&MID(A1;3;20)
I think Sanram has the proper solution. it covers the Padding if need be otherwise just places a comma in the 3 CHAR
12345678 becomes 12,345678Please Login or Register to view this content.
1234567 becomes 12,345670
However, if you want to have the 0 lead, simply move it to the front in the latter half of this format provided by Sanram!
12345678 still becomes 12,345678Please Login or Register to view this content.
but 1234567 now becomes 01,234567
-If you think you are done, Start over - ELeGault
Re: Add comma after 2 digits
Hi! I am so happy I found this thread. I am working on a data import for work quite a few columns come in as XXXXXXXXXXXXXXX when I need them to be comma separated (every 2) to work in the import.
A few things:
- The numbers vary in every row. It could be XXX or XXXXXXXXXXXXXXXXX
- The commas need to be coming from the right so X,XX or X, XX, XX, XX, XX, XX, XX, XX, XX
If the number of digits was the same in every row the solutions in this thread would have helped but because they vary, I cannot find a solution to this. I don't feel like manually adding a comma and space every two digits to thousands of rows of data.
I should add I tried formatting the number as XX, XX, XX, XX in the number format screen but it does not hold, it replaces it to the standard xxx,xxxx
Any ideas?!
Thanks in advance!
Administrative Note:
ElodieHardt Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Dave
Whoops, sorry! Creating a new thread now!
Hello Sir,
20220223 i want in this format 2022/02/23
I want in / after four and again after two digits in excel
can You help me sir
Last edited by kuldeep665; 03-29-2022 at 04:37 PM.
Administrative Note:
Hello kuldeep665. Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
I want to format a cell to put coma after first 2 digits but number of digits may differ from 3 digits to 8 digits. Formulas will not work for me because I need to do it in the same cell. Is there a way to do it?
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks