Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get relative row numbers in a range in Excel
  • How to get last row in mixed data with blanks in Excel
  • Offset in Excel

Data Analysis Examples

  • How To Load Analysis ToolPak in Excel
  • How to Create Thermometer Chart in Excel
  • How to sum a total in multiple Excel tables
  • How To Compare Two Lists in Excel
  • Subtotal function in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation with conditional list
  • Excel Data validation exists in list

Normalize text by removing punctuations, extra spaces and more in Excel

by

To remove some of the natural complexity of text (strip punctuation, normalize case, remove extra spaces) you can use a formula based on the SUBSTITUTE function, with help from the TRIM and LOWER functions.

Instance

There may be times when you need to remove some of the variability of text before other processing.

Case Study

One example is when you want to count specific words inside larger text strings. Because Excel doesn’t provide support for regular expressions, you can’t construct precise matches. For example, if you want to count how many times the word “fox” appears in a cell, you will end up counting “foxes”. You can look for “fox ” (with a space) but that will fail with “fox,” or “fox.” One workaround is to simplify the text first with a formula in a helper column, then run counts on the simplified version. The example on this page shows one way to do this.

Formula

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),
","," "),"."," ")))

Explanation

How this formula works

The formula shown in this example uses a series of nested SUBSTITUTE functions to strip out parentheses, hyphens, colons, semi-colons, exclamation marks, commas, and periods. The process runs from the inside out, with each SUBSTITUTE replacing one character with a single space, then handing off to the next SUBSTITUTE. The inner most SUBSTITUTE removes the left parentheses, and the result is handed to the next SUBSTITUTE, which removes the right parentheses, and so on.

Worked Example:   How to count total words in a cell in Excel
Worked Example:   Convert text date dd/mm/yy to mm/dd/yy in Excel

In the version below, line breaks have been added for readability, and to make it easier to edit replacements. Excel does not care about line breaks in formulas, so you can use the formula as-is.

=
LOWER(
TRIM(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
A1,
"("," "),
")"," "),
"-"," "),
":"," "),
";"," "),
"!"," "),
","," "),
"."," ")))

After all substitutions are complete, the result is run through TRIM to normalize spaces, then the LOWER function to force all text to lowercase.

Worked Example:   Remove leading and trailing spaces from text in one or more cells in Excel

Note: You’ll need to adjust the actual replacements to suit your data.

Adding a leading and trailing space

In some cases you may want to add a space character to the start and end of the cleaned text. For example, if you want to count words precisely, you may want to look for the word surrounded by spaces (i.e. search for ” fox “, ” map “) to avoid false matches. To add a leading and trailing space, just concatenate a space (” “) to the start and end:

=" "&formula&" "

Where “formula” is the longer formula above.

Post navigation

Previous Post:

Get position of 2nd 3rd and more instance of character in Excel

Next Post:

Remove last characters from right in a cell in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to extract domain from email address in Excel
  • How to extract name from email address in Excel
  • Clean and reformat telephone numbers using SUBSTITUTE function in Excel
  • How to Check If A Cell Contains Specific Text in Excel
  • Find Last Day of the Month in Excel
  • How to calculate working days left in month in Excel
  • Get days before a date in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • How to calculate principal for given period in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles