If I need to extract only let­ters from my text, what should I do? Lets find out.

Extract only let­ters from text 

When I need to extract only let­ters from my text, it means that I also need to extract spaces and enters (new lines). 

With Pow­er Query this task is quite easy. 

I need to select one cell in my table, then go to the Data tab, and click on the From Table/Range com­mand in order to take our data to Pow­er Query (Fig. 1)

From Table/Range command
Fig. 1 From Table/Range command

Here, I have four sim­ple pieces of text. One in Eng­lish and three in Pol­ish. We need to go to the Add col­umn tab, then to the Cus­tom col­umn com­mand (Fig. 2)

Custom column command
Fig. 2 Cus­tom col­umn command

In the Cus­tom Col­umn win­dow, we have the space to name our col­umn. Let’s call it Clean, and below we have the Cus­tom col­umn for­mu­la box, where we write the Text.Select func­tion. In the func­tion we need to work with text, so let’s select the right col­umn (1). Then, I need let­ters. Since there will be string of char­ac­ters, I have to use curly brack­ets, where we write upper case let­ter in the first sequence, which is “A”..“Z”. Then let’s write low­er case let­ters, which is “a”..“z”. Writ­ing two dots between let­ters means that there is a sequence. Let’s close the curly brack­et and the paren­the­sis. Now, it should be fine (Fig 3)

Whole function
Fig. 3 Whole function

After press­ing OK, the sys­tem extract­ed for us only let­ters. How­ev­er, the text pre­sent­ed like this is just gib­ber­ish. That’s why we have to add spaces. To do that, we need to go to the View bar, then select the For­mu­la bar check­box and start mod­i­fy­ing our for­mu­la. Let’s write a com­ma and a space in dou­ble quotes (1). If we need oth­er signs, we should also write them, e.g. a dot (2). As we can see, we have spaces and dots in our new text (Fig. 4)

Modifying the formula
Fig. 4 Mod­i­fy­ing the formula

How­ev­er, I don’t need dots in my text. What I real­ly need are enters. We can remove the dot from the for­mu­la and write #(lf) in its place. After enter­ing the for­mu­la, we can see that we have extract­ed enters, low­er case let­ters, spaces. But, I did­n’t extract Pol­ish let­ters. In row 4 there are strict­ly Pol­ish let­ters. In rows 2 and 3 there are some of them. The ques­tion is: how can I extract unusu­al signs from text using the Text.Select func­tion. First of all, we have to check the Uni­code. We can see that let­ters from A to Z is a sequence of num­bers from 65 to 90. It’s sim­i­lar in low­er case let­ters. The most impor­tant les­son from it is that we have a sequence (Fig. 5) 

Unicode
Fig. 5 Unicode

Now, let’s go back to our for­mu­la. If I want to have Pol­ish let­ters, I can write a sequence of “Ó”..“ż”. After enter­ing the for­mu­la, we have extract­ed also Pol­ish signs (Fig. 6)

 Extracting Polish signs
Fig. 6 Extract­ing Pol­ish signs

Now, that we have extract­ed all signs we need, we can go to the Home tab and click on the Close&Load to com­mand to trans­fer our results to Excel (Fig. 7)

Loading results to Excel
Fig. 7 Load­ing results to Excel

In the Import Data win­dow, we have to select a cell (Fig. 7)

Choosing the target cell
Fig. 7 Choos­ing the tar­get cell

As we can see, the text is writ­ten in one line, so we need to wrap it. Let’s go to the Home tab and click on the Wrap Text com­mand (Fig. 8)

Wrap Text command
Fig. 8 Wrap Text command

Now, we clear­ly see upper case let­ter, low­er case let­ter, spaces, enters. I removed signs that I did­n’t need (Fig. 9)

Final table
Fig. 9 Final table

https://www.youtube.com/watch?v=K1KgVudEtnk