Extracting numbers from text

Another common data preparation step is when we need to extract a number from text values. An excellent example is when we want to extract a flat number or a zip code from an address. Other examples include extracting the numeric part of a sales order number or cleaning fullnames of typos, such as when some names contain numbers. In our scenario, we want to add two new columns to the Customer table, as follows:

• Extract Flat Number as a new column from AddressLine1
• Extract the rest of the address, Street Name, as a new column

The AddressLine1 column reveals that the flat number appears in different parts of the address; therefore, splitting by transitioning from digit to non-digit would not work:

Figure 5.43 – Flat Number appears in different places in AddressLine1

To achieve our goal, we need to extract the numbers from text. To do so, we can use the Text.Select(Text as nullable text, SelectChars as any...