Sometimes, our data is combined into one string and we want to extract only a piece of information, which will help us properly understand our data.
How to extract a date number and income from text string with a fixed width
Let’s start with extracting the date. What is important in our example, is that each string of information is of the same length, i.e. it has got a fixed width.
Fig. 1 Data of the same string lengths
We can clearly see, that the date starts with the eighth sign and has got eight signs itself (mm/dd/yy). If we want to extract a certain piece of information, we have to use the MID function. First, we have to write A2 in the argument, because our information is located in this cell. Then, we write 8 because we start with the eighth sign, and then 8 again because we want to extract eight chars. And we have our formula (Fig. 2).
=MID(A2,8,8)
Fig. 2 Formula for extracting a date
After copying it down, we have the results. It’s important though, that the MID function, just like all text functions, returns results as text. We can see it because each date is aligned to the left. It may be problematic if we want to do some date operations. We can change the text by adding 0 to our formula. In other words, we can perform any mathematical operation that won’t change the returned date (Fig. 3).
=MID(A2,8,8)+0
Fig. 3 Adding 0 to dates
Now, we can see that the values are aligned to the right. It means that Excel treats them like numbers, i.e. like dates. We can extract Id numbers the same way. They start with the first sign and have got 6 digits in total. We can also use the MID function. First, we select cell A2, then write 1 because it starts from the first sign, then we write 6 because we extract 6 digits (Fig. 4).
=MID(A2,1,6)
Fig. 4 MID function formula for extracting Id numbers
As we can see, Excel extracted Id numbers. Since it is text, Excel left the leading zero for us. We can leave Id numbers as text if it’s important for us. If it isn’t, we can do the same as with dates, i.e. add zero (Fig. 5).
=MID(A2,1,6)+0
Fig. 5 Adding 0 to Id numbers
We can see that Id numbers moved to the right and the leading zero vanished. The last piece of information we want to extract is the Income. Income values don’t have a fixed width. But, since it’s the last piece of information, we can extract more signs than we need. Let’s extract also the dollar sign. The dollar sign is in the seventeenth position. We start writing the MID formula, then we add cell A2, then 17, and then we have to add the number of places we want too extract. Let’s write 10. It’s, in fact, more that we need, however it’s not less either. The MID function will extract the information to the end without any errors (Fig. 6).
=MID(A2,17,10)
Fig. 6 MID function for extracting Income
As in our previous formulas, the results are also text. By adding zero, we change the results into numbers.
MID(A2,17,10)+0
Fig. 7 Text into numbers
We can see that now the results are numbers. However, we can see that we lost the dollar sign. This is the way Excel converts numbers with dollar signs before them. Indeed, the dollar sign isn’t important in our data, so we should start from the eighteenth sign (Fig. 8).
Fig. 8 Removing the dollar sign
Now, we have only numbers. If we want to add a currency, we just go to the Home tab, drop down theGeneral bar and select the Currency option (Fig. 9).
Fig. 9 Adding a currency
When we have our results with zeros at the end, we can remove them by pressing the Decrease Decimal option (Fig. 10).
Today, we want to extract the last row with given currencies. Our data is sorted randomly. If we want to consider the date column, we will have to sort it first. We can do it manually, by going to the end of date and find the last row. However, we want to sort it in Power Query, which is more automatic. We just have to select one cell from our table, go to Data tab, and click on the From Table/Range command (Fig. 1).
How to extract last rowFig. 1 From Table/Range command
This command exports our data to Power Query. We can see, that Power Query considers our data as a date and time. In our example, it isn’t important so let’s leave it like that. If we want to sort our dates, we just click on the A to Z sorting command on the Home tab (Fig. 2).
Fig. 2 A to Z sorting command
Now, the last row is the newest date (Fig. 3).
Fig. 3 The newest date in the last row
Since we are interested in currencies, we want to group our data by the Currency column. We have to click one cell from the Currency columns and go to the Group By command in the Home tab. In the Group By window that has appeared, we can see that the Currency bar is selected. In the Operation bar, we need to select All Rows and, in the New column name, let’s write temporary. Then we are pressing the OK button (Fig. 4).
Fig. 4 Group By window
And we have our data grouped by currencies (Fig. 5).
Fig. 5 Data grouped by currencies
There is a table for each currency that contains all rows with a given currency. We have sorted the dates earlier, so in the tables, the last date is also the newest exchange rate. And the newest exchange rate is what we want (Fig. 6).
Fig. 6 The newest exchange rate
Let’s go to the Add Column tab, then to the Custom Column command. A window will appear, where we write the name. Let’s call it Last. Then, we need to write a formula in the Custom column formula box. We are writing Table.LastN command to attract the last row of the table. The function we are writing needs a table, so we are choosing our table called temporary, then we need to write the number of rows from the end. We want just one row, so let’s write it, then close the formula. This is the whole formula we need, so let’s press the OK button (Fig. 7).
=Table.LastN(temporary),1)
Fig. 7 The formula
We can see that we have the Last column. In the column, there are tables, but each table contains only one row for each currency (Fig. 8).
Fig. 8 The Last column
We don’t need the temporary column, so let’s delete it (Fig. 9).
Fig. 9 Deleting the temporary column
Now, we can expand the Last column by pressing the icon shown in Fig. 10. In the window that has appeared, we deselect the Currency and the Use original column name as prefix checkboxes. Then, we press the OK button (Fig. 10).
Fig. 10 Expanding the Last column
Now, we can see a proper table with currency, and the newest date with the exchange rate. Let’s go to the Home tab, and Close&Load to command (Fig. 11).
Fig. 11 Close&Load to command
We moved back to Excel, where we have to select the Existing worksheet radio button, then click on cell F1 and deselect the Add this data to the Data Model checkbox, and press the OK button (Fig. 12).
Fig. 12 Import Data
And we have our results. However, we have to remember that Power Query not always extracts proper data formatting. That’s why we have to go to the Home tab and select proper data formatting (Fig. 13).
Fig. 13 Results with proper dates
Let’s make a test and change some some dates in different currencies, then refresh our table (Fig. 14).
Fig. 14 Refreshing the table
We can see that the Query is automatic when we refresh it (Fig. 15).
