Extracting only letters from text

by | Feb 8, 2024 | Excel

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

Hiding scroll bars, formula bars and column headers

by | Feb 5, 2024 | Excel

When we need more space while work­ing in Dash­board in Excel, we can just hide unnec­es­sary options. How? Let’s find out.

Hide scroll bars, for­mu­la bars, col­umn headers

The options we can hide are e.g. scroll bars, sheet tabs, for­mu­la bars as well as column/row head­ers. If we want to hide then we just go to the File menu and choose the Options com­mand (Fig. 1)

Options
Fig. 1 Options 

In the Excel Options win­dow, we choose the Advanced tag. If you pre­fer using short­cuts, you can press Alt + T + O, which also opens the win­dow. Lets go to the Dis­play area and uncheck the Show for­mu­la bar check­box. Remem­ber that it works for all Excel files (Fig.2 )

Show formula bar checkbox
Fig. 2 Show for­mu­la bar checkbox

Then, we can go to the Dis­play options for this work­book area. We have to uncheck the Show hor­i­zon­tal scroll bar, Show ver­ti­cal scroll bar, Show sheet tabs check­box­es (Fig. 3)

Three checkboxes
Fig. 3 Three checkboxes

We can even go to the Dis­play options for this work­sheet area where we can uncheck the Show row and col­umn head­ers check­box and press OK (Fig. 4)

Show row and column headers checkbox
Fig. 4 Show row and col­umn head­ers checkbox

We can also dou­ble-click on the Home tab to hide the rib­bon, which gives us even more space than before. 

Now, I can press Ctrl + Page Down to go to anoth­er work­book. Here, we can see that col­umn and row head­ers are vis­i­ble. How­ev­er, when we go back to our work­book by press­ing Ctrl + Page Up, we can see that there are no row or col­umn head­ers. That’s how you make more space in your Dashboard. 

https://www.youtube.com/watch?v=2TNzX5RSG7A&t

Drop-down lists

by | Feb 1, 2024 | Excel

How to add a drop-down list in Excel? It’s a real­ly sim­ple task. 

First of all, we have to have our list (1). Then, we have to select a cell or cells to which we want to add the drop-down list (2). Then, we’re going to the Data tab (3) and click on the Data Val­i­da­tion com­mand (4) (Fig. 1)

Preparing data
Fig. 1 Prepar­ing data

In the Data Val­i­da­tion win­dow, we have to select the List option in the Allow bar, then we’re select­ing the range and write it into the Source bar. Then OK (Fig. 3)

Data Validation window
Fig. 2 Data Val­i­da­tion window

Now, we have a drop-down list (Fig. 3)

A drop-down list
Fig. 3 A drop-down list

Since our cells with data are in a table, when we add a new line to the table, it also involves the drop-down list. 

Even, if we cut and paste our new data, the cells in the select­ed col­umn will still have the drop-down list. What’s more, in the newest Excel ver­sion, we have even Lookup options. We just have to start writ­ing the first let­ter of a word we’re look­ing for, then press the Tab key.

Lookup option
Fig. 4 Lookup option

This func­tion, how­ev­er, does­n’t work when we start from the mid­dle of our text. It works only with the first letter. 

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