Today's question comes from Mary, in Pennsylvania. Mary received an Excel Workbook file from a subsidiary company in Latin America and the Data is not in the correct format to match Mary's current file. The new file contains a decimal and a comma - Mary's file is Formatted for only the comma. In Episode #1562, Bill shows a quick way to select and reformat all of this Data to bring it to the correct Format or how to easily changed both Columns of Data to domestic currency.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast episode 1562 Latin America 1.234,56.
Hey, welcome back to the MrExcel netcast.
I'm bill Jelen.
Today's question sent in by Mary from Pennsylvania.
Mary got a file from their Latin American subsidiary, has this whole huge big column of numbers that are in the wrong format.
See this with the 5.5 90 comma 57.
So they're using comma for the decimal point and a period for the thousand suffer.
Now actually Mary’s data was like this over here and it's much simpler if you don't have that decimal point.
You don't have a decimal point, check this out.
I'm going to hold down ctrl shift down arrow that selects the whole column control H4 find and replace.
I'm going to find every comma and replace with a period, click replace all and we are good to go.
All right, you can now do a math with that but if you have data like this with a thousand separator there how you don't want to be really convoluted.
You'd have to change the comma to like a semicolon, change the period to a comma and then change the semicolon back to a period.
But there's a better way Control Shift Down Arrow to select the range then out on the Data Tab text to columns I'm going to say “Delimited”.
I'm going to make sure that comma is not selected, tab is good because there are no tabs in the data.
I want to have just a single field click Next, all right here is the magic in step 3.
Check this out the Advanced button but you never checked out the Advanced button, it says - hey to recognize numeric data the decimal separator is not a period, it's a comma, the thousands separator is not a comma, it is a period.
I'll check that it, there's even must be some system that uses an apostrophe, alright.
So, period four thousand separator, comma four decimal separator and this doesn't affect your settings at all right.
You're still going to get whatever numbers displayed as the regional settings.
So it just says – hey, for this text this is how you recognize where the thousand separator and the decimal separator is.
So we click Ok, click finish and bam, good to go.
Alright, love that one.
I want to thank Mary for sending that question in.
Well, thank you for stopping by.
I will see you next time for the other netcast, MrExcel.
Learn Excel from MrExcel Podcast episode 1562 Latin America 1.234,56.
Hey, welcome back to the MrExcel netcast.
I'm bill Jelen.
Today's question sent in by Mary from Pennsylvania.
Mary got a file from their Latin American subsidiary, has this whole huge big column of numbers that are in the wrong format.
See this with the 5.5 90 comma 57.
So they're using comma for the decimal point and a period for the thousand suffer.
Now actually Mary’s data was like this over here and it's much simpler if you don't have that decimal point.
You don't have a decimal point, check this out.
I'm going to hold down ctrl shift down arrow that selects the whole column control H4 find and replace.
I'm going to find every comma and replace with a period, click replace all and we are good to go.
All right, you can now do a math with that but if you have data like this with a thousand separator there how you don't want to be really convoluted.
You'd have to change the comma to like a semicolon, change the period to a comma and then change the semicolon back to a period.
But there's a better way Control Shift Down Arrow to select the range then out on the Data Tab text to columns I'm going to say “Delimited”.
I'm going to make sure that comma is not selected, tab is good because there are no tabs in the data.
I want to have just a single field click Next, all right here is the magic in step 3.
Check this out the Advanced button but you never checked out the Advanced button, it says - hey to recognize numeric data the decimal separator is not a period, it's a comma, the thousands separator is not a comma, it is a period.
I'll check that it, there's even must be some system that uses an apostrophe, alright.
So, period four thousand separator, comma four decimal separator and this doesn't affect your settings at all right.
You're still going to get whatever numbers displayed as the regional settings.
So it just says – hey, for this text this is how you recognize where the thousand separator and the decimal separator is.
So we click Ok, click finish and bam, good to go.
Alright, love that one.
I want to thank Mary for sending that question in.
Well, thank you for stopping by.
I will see you next time for the other netcast, MrExcel.
