Fresh Excel Tips

Previously, we had used the SUBSTITUTE function to replace characters in a cell, you can view that tutorial HERE. Today, we’ll be using this function to replace “-” with a blank space, not only that but we want only the first occurrence of it to be replaced. For example, we have 333-444-555, the value we want returned is 333444-555. Here is how we will achieve this. This should work in Excel 97, 2000, 2003 and 2007

How to substitute parts of a cell in Excel

1. Enter your values in Column A1, for example, 123-456-7890
2. Select cells B1:B10 and type the following formula: =SUBSTITUTE(A1,”-”,”",1).
3. Press CTRL+Enter.
4. If you want to substitute the second occurrence of this character, use the following formula: =SUBSTITUTE(A1,”-”,”",2)

Tell us what you think.

1. Using the SUBSTITUTE function to substitute characters in Excel
2. How to Use the REPLACE function in Excel
3. How to remove word wrapping in cells using Excel
4. How to Separate Cell Values Using the Trim Function in Excel
5. How to Extract digits from a cell using the MID Function in Excel