How to Use Power Query to Edit Text in Microsoft Excel

There’s more than one way to make text edits in Microsoft Excel. From changing letter case to adding a prefix, you can use Power Query to edit text in bulk. This is a great alternative to manual work or functions and formulas.

How to Edit Your Text with Power Query

To edit your text using Excel Power Query, you’ll follow the same steps to get started. Once you open the Power Query Editor, you can make one or more changes before transferring the data back to your spreadsheet.

1. Select the cells containing the text you want to change, go to the Data tab, and choose From Table/Range in the Get & Transform Data section. Alternatively, you can right-click and pick Get Data From Table/Range.

2. If your data is not already formatted as a table, you’ll be prompted to do so. Confirm the cell range in the pop-up window and optionally check the box if you have headers. Then, select OK to continue.

Tip: You can revert your data back to a cell range by deleting the table in Excel later if you like.

3. You’ll then see the Power Query Editor open displaying your data. Select the column(s) you want to change and go to the Transform tab to work with the Text Column section.

Change Letter Case With Power Query

If you want to use Power Query for text formatting like changing the letter case, you have a few different options. You can make the text all lowercase or uppercase as well as capitalize each word in the string.

1. Open the Format drop-down menu and pick the letter case you want to use. You can choose Lowercase, Uppercase, or Capitalize Each Word.

2. You’ll see your selected column update to the letter case of your choice. If it looks good, head to the Home tab and open Close & Load or the File menu to load the new data into your worksheet.

3. Then, pick one of the following options:
4. Close & Load: Use the default settings which load the dataset as a table on a new sheet in the workbook.
5. Close & Load To: Use the dialog box to choose how to load the data such as a PivotTable Report for data analysis, in the existing worksheet or a new one, or add the data to a Data Model.

When the Power Query Editor closes, you should see your updated text allowing you to remove or replace the original text.

Trim or Clean Text With Power Query

Another helpful way to use Power Query for text values is if you want to clean up the data.

With the Trim feature, you can remove leading and trailing spaces like extra space at the start of the text. Note that, unlike the Trim function, this feature does not remove additional spaces between characters.

With the Clean feature, you can remove non-printable characters like tabs or code at the end of the data.

Open the Format menu and pick Trim or Clean and you’ll see your text update. Note that if you use the Clean option, you may not notice an obvious difference because of the non-printable characters.

When you finish, go to the Home tab and open Close & Load or use the File menu. Then, choose an option to load the updated data into your sheet.

Add a Prefix or Suffix With Power Query

One more useful edit that you can make to text with Power Query is to add a prefix or suffix. For instance, you may want to add “Dr.” at the beginning or “Ph.D.” at the end of a list of names.

1. Open the Format menu and pick Add Prefix or Add Suffix.

2. Enter the prefix or suffix in the pop-up window and be sure to include any spaces as necessary. For example, you may want to add a space after the prefix or before the suffix.

3. Select OK to apply the change and you’ll see your text update. Then, head to the Home tab and select Close & Load, or use File menu to pick an option to load your edited data.

Take Control With Power Query in Excel

You might not think to use Power Query to edit text in Excel, but it can be an efficient and effective tool for such changes. This is also a great way to become more familiar with Power Query and the editor’s user interface. You can then perform even more data visualization and manipulation actions with it down the road.

Having trouble entering data in your sheet? Take a look at these fixes for when you can’t type in your Excel file.