A column of currency ended up with commas for both the thousands separator and the decimal point! How can you replace the last comma
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2421 - Change the last comma to a period.
Welcome back to MrExcel netcast.
I am Bill Jelen. Today's question in a comment at YouTube - they had numbers, R1,000,00 and they wanted to change the last comma to a decimal point.
Thinking about that there might be numbers that are larger.
So you actually have 3 commas and you want to change the third comma.
Or here: smaller number, there's only one comma, and that's the comma you want to change.
So I have two different solutions to this and I'm curious to see what your solution would be.
The first thing I wanted to do was go into text to columns, and in the step three use Advanced and see if I could play with these, but it's not working the way that I want it to work.
It's probably working in the correct way, it just didn't work out for me.
So a trick that we've used before we take the LEN of A2 and then subtract the LEN of A2 after we change all the commas to nothing.
So the difference between those two lengths tells us how many commas we have.
And then the awesome thing is the SUBSTITUTE function can say replace a comma with a period.
This 4th argument is which instance do we want to change.
So in this case we want to change the first comma, but out here we want to change the third comma thanks to that formula.
Now I do that in two pieces because I want you to understand how this is working.
But once you understand it then really you can just come back here and put it all in one big long formula.
But there is definitely another way to go.
And my my first step is, let's just get rid of all the commas.
We can put it back later.
And so the SUBSTITUE to get rid of every comma, change it to nothing.
But we still have an R and I actually tried to put an array constant in here and it didn't work the way I wanted it to work.
Maybe with LET?
I don't know.
Alright, so my cheap way of doing this is just assume that everything starts with an R. I could have done another SUBSTITUTE.
So the MID of A2 starting at 2 for a length of 50.
That gets rid of the R.
But we're still text and it's still multiplied by 100.
This was 39.40 and now I have 3940.
And we can solve both of those problems simply by just dividing by 100.
The math operation coerces the text back into a number and then dividing by 100 gets it back to the original amount.
From this point, if we want those numbers as numbers, then come into the number dialog launcher, go to Currency.
In the symbol, scroll all the way down to R. English South Africa will work.
Click OK and you get everything formatted.
If you actually need it to be text… the question would have left it as text - then we can either use the TEXT function with backslash R or the TEXT function with this dollar sign R NZ.
Both will work.
For me, that's the shortest one.
It is easiest to explain.
And it has the advantage that we can actually SUM things.
Here we couldn't SUM that.
But here using the number format we can sum and it will be formatted correctly.
Check out my new book MrExcel 2021 Unmasking Excel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Welcome back to MrExcel netcast.
I am Bill Jelen. Today's question in a comment at YouTube - they had numbers, R1,000,00 and they wanted to change the last comma to a decimal point.
Thinking about that there might be numbers that are larger.
So you actually have 3 commas and you want to change the third comma.
Or here: smaller number, there's only one comma, and that's the comma you want to change.
So I have two different solutions to this and I'm curious to see what your solution would be.
The first thing I wanted to do was go into text to columns, and in the step three use Advanced and see if I could play with these, but it's not working the way that I want it to work.
It's probably working in the correct way, it just didn't work out for me.
So a trick that we've used before we take the LEN of A2 and then subtract the LEN of A2 after we change all the commas to nothing.
So the difference between those two lengths tells us how many commas we have.
And then the awesome thing is the SUBSTITUTE function can say replace a comma with a period.
This 4th argument is which instance do we want to change.
So in this case we want to change the first comma, but out here we want to change the third comma thanks to that formula.
Now I do that in two pieces because I want you to understand how this is working.
But once you understand it then really you can just come back here and put it all in one big long formula.
But there is definitely another way to go.
And my my first step is, let's just get rid of all the commas.
We can put it back later.
And so the SUBSTITUE to get rid of every comma, change it to nothing.
But we still have an R and I actually tried to put an array constant in here and it didn't work the way I wanted it to work.
Maybe with LET?
I don't know.
Alright, so my cheap way of doing this is just assume that everything starts with an R. I could have done another SUBSTITUTE.
So the MID of A2 starting at 2 for a length of 50.
That gets rid of the R.
But we're still text and it's still multiplied by 100.
This was 39.40 and now I have 3940.
And we can solve both of those problems simply by just dividing by 100.
The math operation coerces the text back into a number and then dividing by 100 gets it back to the original amount.
From this point, if we want those numbers as numbers, then come into the number dialog launcher, go to Currency.
In the symbol, scroll all the way down to R. English South Africa will work.
Click OK and you get everything formatted.
If you actually need it to be text… the question would have left it as text - then we can either use the TEXT function with backslash R or the TEXT function with this dollar sign R NZ.
Both will work.
For me, that's the shortest one.
It is easiest to explain.
And it has the advantage that we can actually SUM things.
Here we couldn't SUM that.
But here using the number format we can sum and it will be formatted correctly.
Check out my new book MrExcel 2021 Unmasking Excel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
