How to extract Number and Text (Separate) from Alphanumeric Data using Power Query is explained in this post.
Following is the Alphanumeric data which we are going to work with…
To extract Number and Text from the above data, into separate columns,
Select the data > Click on From Table/Range in the Data tab of Excel ribbon.
When you click OK in the Create Table dialog, the selected data range will be converted into an Excel Table and Power Query Editor will be activated.
Select the column containing data > Click on Column From Examples in the Add Column Tab > Select From Selection
Add Colum from Examples option is activated.
The Add Column from Examples is powered by the Intellisense of Excel, something similar to Flash Fill. (In Flash Fill, we show a pattern to Excel and Excel will produce data following that pattern)
Now, in the column named Column1, we have to type in some values. By analyzing these sample values, Excel will detect the expected Output.
I will type in the value 123 in the first row and the following is the detected pattern. But this is not enough to extract numbers from Alphanumeric Data.
I will type in the value 24 in the fourth row. When I press the Enter key, the pattern is once again modified. All Numerals present in the source data are now shown in the column called Kept Characters, which is exactly what we are looking for.
Click OK to confirm and a new Column is created which contains the extracted numerals from the Alphanumeric data.
To extract the Text data, once again the select the column containing Alphanumeric Data > Click on Column From Examples > Select From Selection
Once again, Add Colum From Examples option is activated.
Type in some sample values in Column1 so that Excel can detect the pattern of the expected Output.
I will type in ABC in the first ROW and Excel has detected the following pattern. This is nothing but the first few letters from every cell. But this not enough to extract every text character present in the source data.
Type in ABC in the second row and the following is the modified pattern. It looks good but still needs modification as some rows are still having some numbers in it.
Type in ATT in the 4th row. When you press the Enter Key the pattern is modified, which is pretty good enough to extract the text characters from Alphanumeric data.
Click OK and a new column is added which contains the text characters extracted from the Alphanumeric data.
Remove the first column and rename the column headers of columns containing Numbers and Text
Click on Close and Load To
Use the Import Data dialog to load this data into the column adjacent to the column containing source data.
Specify the address of the destination cell, in the input box under the Question, Where do you want to put the data?
Click OK and the extracted data will be loaded into the specified cell.
As you may know, Refreshing the query will update the table for new additions.
You can see the text Office 365 and AutoCAD 2019 added to the end of the table.
Click on Refresh All in the Data tab
The table is updated for the newly added data. i.e. Text and number from Office 365 and AutoCAD 2019 are extracted into separate columns.