Using the SUBSTITUTE function to substitute parts of a cell
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
- Enter your values in Column A1, for example, 123-456-7890
- Select cells B1:B10 and type the following formula: =SUBSTITUTE(A1,”-”,”",1).
- Press CTRL+Enter.
- If you want to substitute the second occurrence of this character, use the following formula: =SUBSTITUTE(A1,”-”,”",2)
Tell us what you think.