"Share this Info and Help a Friend"

How to Use Power Query to Edit Excel

AD: We want to be your IT and Technology Provider!
No Contract Prices, IT, Cellphone, TV, VOD, Phone, Internet.
"You have Nothing to Lose But your Higher Bill"
https://www.teqiq.com/

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.

  1. 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.

 

If this tip helps and you would like to donate click on the button. Thanks In Advance.

________________________________________________________________________________________________________

"Fortune Favors, Who Value Time over Money!"

"TeQ I.Q. was the 1st IT Company to Deliver Cloud Solutions since 2003"
Tech issues taking up your Time?
"TeQ I.Q. Makes Your Technology Secure and Protected"
Do you have Tech Frustrations like your Computer, Internet, Phone, Cellphone, Camera, TV, Car?

"We Take Away Your Tech Frustrations and Give You the Free Time You Deserve!"
Call Robert to ask all your Technology questions.

For Free Consultation Call Now Robert Black at (619) 255-4180 or visit our website https://www.teqiq.com/

Chase Bank and Others Trust TeQ I.Q. with their IT and TeQnology so can you!