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.

Tip: You can revert your data back to a cell range bydeleting the table in Excellater if you like.

Change Letter Case With Power Query

If you want to use Power Query for text formatting likechanging 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.

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 fortext valuesis 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 theFormatmenu and pickTrimorCleanand 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 theHometab and openClose & Loador 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.

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 thesefixes for when you can’t type in your Excel file.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved