Simplifying Multiple Substitute Formulas in Excel

How do I simplify this formula to delete all repeated - in Excel?

Cell A1: text---------------text-text---text

Cell A2: text----text--text---text

I need it to be:

Cell A1: text-text-text-text

Formula I am using:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"--","-"),"--","-"),"--","-"),"--","-")

I was wondering if there is a way with same SUBSTITUTE formula to do something like 4*SUBSTITUTE(A1,"--","-") so it makes the job 4 times without a huge formula.

Thanks

3 Answers

in cell b1: =SUBSTITUTE(A1,"--","-")you can then copy this across to c1, d1, e1 etc. Each further cell to the right will have one less - than the previous. Add columns until all hyphens are dealt with.

This way if you add a new row where there are more hyphens than in previous rows, you can just make extra columns, instead of changing the formula which gets messy.

A tiny trick for a single cell solution. If A1 does not contain any spaces, in A2 enter:

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"-"," "))," ","-")

This can handle any number of consecutive dashes:

If A1 does contain some spaces, a slightly more complex formula will be required!

EDIT#1:

If A1 contains spaces, they must first be "protected" with something like:

=SUBSTITUTE(A1," ",CHAR(1))

and then, in the outer-most substitution, the "protection" must be removed. The final formula is:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",CHAR(1)),"-"," "))," ","-"),CHAR(1)," ")

I will admit that this is pretty ugly, but it can handle an unlimited number of dashes.

If you think your needs might change, use a lookup table

The website exceljet.com had a very good way of handling this by Dave Bruns. This works very well when you'd like to see your corrections laid out in a table, and edit them or change them.

It avoids hard coding all the corrections into formulas, which can be hard to verify (and impossible when printed on paper).

It works by using the INDEX function. Create a lookup table:

before | after
--------- | ---------
APPLE | ALICE
BANANA | BOB
CHERRY | CARL
KIWI | KENNY

• Use Insert:Table to tell Excel it's a table, and let's name it corrections:

Then in your main table, use this formula:

=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
J2,
INDEX(corrections[before],1),INDEX(corrections[after],1)),
INDEX(corrections[before],2),INDEX(corrections[after],2)),
INDEX(corrections[before],3),INDEX(corrections[after],3)),
INDEX(corrections[before],4),INDEX(corrections[after],4))

• Yes, you're allowed to have 'returns' (linefeeds) in a formula. Excel ignores them, but they make it much easier to read.

• There's no magic in the numbers 1,2,3,4... they are the row numbers in the corrections table for the INDEX function.

• The number of rows (4) in the lookup table is the same as the number 4 in the formula. If you need more rows, read on:

Expand as necessary

If you need to add more rows to your corrections table, it's very easy. Just modify your replacement formula. Let's say you want to go up to eight rows:

=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
J2,
INDEX(corrections[before],1),INDEX(corrections[after],1)),
INDEX(corrections[before],2),INDEX(corrections[after],2)),
INDEX(corrections[before],3),INDEX(corrections[after],3)),
INDEX(corrections[before],4),INDEX(corrections[after],4)),
INDEX(corrections[before],5),INDEX(corrections[after],5)),
INDEX(corrections[before],6),INDEX(corrections[after],6)),
INDEX(corrections[before],7),INDEX(corrections[after],7)),
INDEX(corrections[before],8),INDEX(corrections[after],8))