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

Lookup and Reference Examples

  • Left Lookup in Excel
  • Extract all partial matches in Excel
  • How to create dynamic named range with OFFSET in Excel
  • Find closest match in Excel
  • How to reference named range different sheet in Excel

Data Analysis Examples

  • Conditional Formatting New Rule with Formulas in Excel
  • Chart Axes in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How to calculate current stock or inventory in Excel
  • Understanding Pivot Tables in Excel

Data Validation Examples

  • Excel Data validation number multiple 100
  • Excel Data validation don’t exceed total
  • Excel Data validation must contain specific text
  • How To Create Drop-down List in Excel
  • Excel Data validation date in next 30 days

Replace one character with another in Excel

by

To replace or substitute all occurrences of one character with another character, you can use the SUBSTITUTE function.

Formula

=SUBSTITUTE(ref,old,new)

Explanation

In the example shown, the formula in C6 is:

Worked Example:   Convert column number to letter in Excel
=SUBSTITUTE(B6," ","-")

How this formula works

The SUBSTITUTE function is full automatic. All you need to do is supply “old text” and “new text”. SUBSTITUTE will replace every instance of the old text with the new text.

Worked Example:   Remove line breaks in a cell in Excel

If you need to perform more than one replacement at the same time, you’ll need to nest multiple SUBSTITUTE functions. See the “clean telephone numbers” example linked below.

Worked Example:   How to count specific words in a range in Excel

Post navigation

Previous Post:

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

Next Post:

Split dimensions into three parts 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
  • How to strip non-numeric characters in Excel
  • How to create email address with name and domain in Excel
  • How to check if cell contains one of many things in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to pad text to match equal length in Excel
  • Add business days to date in Excel
  • How to get Holiday Date from Year in Excel
  • Series of dates by day
  • Convert date string to date time in Excel
  • Add months to date in Excel
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • Compound Annual Growth Rate CAGR formula examples in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles