Excel training courses – STL

If you use mailmerge in Word, often the source of contact details comes from Excel. And there are times when you inherit contact details from another project or colleague, and the names may not be separated.

This can be a heart-sink moment as you need to separate each name individually. Argh…this is going to take hours….or in the style of a panto…”oh no it doesn’t!”

How to separate names in Excel – Text to Columns

This is when Excel can save you hours, and possibly even days…you can separate names using Text To Columns.

Here is a seasonal list of names I need to separate

Names-to-change-excel-training-courses — List of a few names that need to be separated before I can mail merge.

In Excel then you can separate the names using the Text to Columns feature on the Data tab.

1.First insert 2 blank columns to the right of your Names column.

insert-columns-excel-training-columns — You will need two columns for your sort to work.

2. Highlight the names in column A and then go to the Data tab, and select Text to Columns.

data-tab-excel-training-courses — The Text to Columns button is on the Data tab, in the Data Tools

dialogue-box-excel-training-courses — To split the names, you need to use the space between the first name and surname, and the result will be shown in the small preview screen

In the dialogue box, select Delimited.

3. Click on space as the first names and surnames are separated by a space.

4. Choose a destination cell at the top of the first inserted column. In our example, we want to use the adjacent cells in column B and C.

destination-cells-excel-training-courses — Use your blank columns as the destination cells

5. Press ok. The data will then be broken up where ever a space occurs.

sorted-names-excel-training-courses — Sorted!

This technique is included in our Excel training courses and can help you with your Word and Excel work.

One of our trainers showed me this function when I was trying to find data in a huge worksheet. It is a fantastic function for List Management and saves me lots of time and stressing.

Here is a simple example to demonstrate how to use filters in Excel.

I have my worksheet with details of budgeted items for a business trip. I want to try and identify the one-off items to see if I can reduce those costs.

Here is my worksheet with details of the items and units. I want to filter the results by cost per person.

budget-with-per-unit-excel-training-courses — Here is my budget worksheet with the full list of items and costs.

I can filter the items by the ones that match my search criteria, by using the the Filter option in the Data tab.

Data-tab-filter-excel-training-courses — The Data tab has the Filter option ready to go…

To filter my budget, I put my cursor on the heading for per unit, and then click on the Filter icon. I know the filter has been applied as down arrows appear next to the heading titles.

filter-applied-headings-excel-training-courses — Filters applied, and to show me that it’s ready to go, here are the little down arrows.

I select the down arrow next to the per unit heading

filter-options-excel-training-courses — Filter options available to mix and match

I select the per person filter, by removing the tick from select all and then tick per person, and click OK. Here are my results.

filter-per-person-excel-training-course — The filter results confirm two items.

When I want to see all my items listed again, I click on the down arrow next to heading and select Clear filter from per unit.

remove-filter-excel-training-courses — I can return to all my items by removing the filter.

Remember: It is possible to forget you have the filter on, and then have a minor moment of panic when you think you’ve lost data…I think Excel knows this can happen….so look out for the down arrow next to your heading as a reminder. Excel is thoughtful like that!

Using filters is included in our Excel Intermediate courses under List Management. For more information on our Excel training courses and the course syllabus, please see https://www.stl-training.co.uk/excel-2010-intermediate.php

Blog Categories