Hello and welcome to this video tutorial from Computergaga.com. And in this
video we are going to cover two simple ways of working with international
number formats. So in this example, here in the UK we use a decimal point as a
separator. Where many other countries in the world will use a decimal comma. So
when we're working with numbers received from such countries, there can be some
confusion in Excel. Now there's multiple ways of handling this, including setting
up default settings in Excel. But here I want to cover two very simple ways. So on
screen I have some pretend sales data from a store in Gdansk in Poland. And
they use a decimal comma. So Excel does not understand what these numbers are. It's
put them on the left, storing them as text. I need to convert them to numbers.
I cannot sum or chart any of these values until that's done. So here comes
simple example number 1. And we can select column B, the column with the
values, and open up the Find and Replace window of Excel. Either by using the
button on the Home tab, or the keyboard shortcut - Ctrl + H. And with the
Replace window open. I can perform my first find and replace, where I will look
for a full stop. Because that has been used in two of them as a thousand
separator. But once again that confuses Excel, or my version of Excel, because it
believes it to be a sentence and a full stop.
And it's not necessary here. It's just to make it look easier to read. I'm going to
replace it with nothing. And when I do that, out it goes. I'll click OK to the
confirmation message. And with a second find them replace. Instead of the full
stop, I will look for the comma. So that's our decimal separator now. And replace it
with a decimal point separator. And notice all those values are on the left
hand side. When I click Replace All. They are thrown to the right as Excel now
recognizes them as numbers. I can now click OK, close down my window, and then
format these further as appropriate. And use them in further data analysis tasks.
So that's our first example. Simple example of working with an international
number and being able to convert it quickly for use. And that's great if
you've just got values coming in via spreadsheets in, and you need to quickly
change them. Because that will just take you a few seconds. But what if it's a
more regular occurrence. What if the values in this range were changed
regularly. I don't want to keep having to run Find and Replace,
however quick it might be. Or what if we need to perform multiple transformation
tasks. Not just converting number formats, but also removing duplicates or renaming
column headers. Or what if the data is coming from an external source like a
CSV file, or from a web page. Well in examples like that, rather than the quick
Find and Replace, we could use Power Query. So if I click
somewhere in that range of values. And on the Data tab,
click From Table/Range. But I could grab this data from other external sources
immediately. It doesn't need to be in my spreadsheet. But let's imagine for a
moment that this is how I receive the data. I'll click OK
to open the Power Query Editor where I can perform numerous tasks. To start with
we could rename the query on the right hand side if it the situation was more
elaborate maybe than this. But just to focus on the purpose of this video. I
need to convert these into numbers. Now I will start by selecting the column 2
here, my values column. And on the Home tab, a Replace Values button. Where I'm
going to do the same thing as we did initially with Find and Replace. I'm
going to ask it to find that full stop, and replace it with nothing. Because that
will still cause us problems when converting this. When I click OK the full
stop is removed, and it is added as a step over on the right-hand side. So for
anybody who might be new to Power Query watching this. And you're thinking cor it
was so much faster with the Find and Replace one. Why would I go for all this.
I've still got to convert them yet. But this will be better in a more elaborate
solution. Where you have more transformations to do. So we can do them all here and
refresh them with just one click of a button, or get them from the external
source directly. And I want to be clear on that. Because obviously the speed of
this is not comparing to the previous tip at the moment. We now need to change
the locale. So if I click on this ABC icon which illustrates the number
formatting. That it's still stored as text. And if I click on that I have other
options like currency, date and time etc. But I want using
locale. Here I can specify the origin of these values. I will choose currency.
That's the type of number. This can be really useful when converting
international dates as well though. And then for the locale, rather than scroll
through this list. I'm going to type a P to jump to the P's. It's just off screen.
And I can find Polish (Poland) a little bit quicker than my scrolling. If I then
click OK. They are happily converted into
something that my Excel recognises. Now this is not the formatting, it's a data
type. Just be clear on that. So just like with Find and Replace. I'll
still need to format them as currency after. I'm just putting this into a
recognisable data type. I now need to Close and Load this back to my worksheet.
I can zoom out of this now. And then this will be refresh-able in the future, nice
and easily. If I just put this into a table in the existing worksheet for
this demonstration. And here it comes and here it is. So in the future if any of those
numbers was to change. If product B 43.99 was to become 500 and let's do 76.
Then I can either come to my Queries and Connections pane on the right. Or the
Data tab at the top. Just refresh my data. And here is the update. So it would be
quicker to update in the future than performing a find and replace sequence.
And we could have done a lot more with that as well. So we've got two simple
ways of working with international number formats there.
No VBA, no complex Excel formulas. Just good old Find and Replace, and then the
fantastic Power Query solution. I hope you found this video useful. Please check
out some of our other video tutorials on our YouTube channel. And
come check us out at Computergaga.com
you