1

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

4 Answers 4

1

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.

1

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:

enter image description here

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.

3
  • Hello, thanks. How is formula when containing spaces? Thanks
    – Joao
    Commented Jan 17, 2018 at 13:52
  • @Joao See my EDIT#1 Commented Jan 17, 2018 at 13:59
  • Hello Gary, I tried all formulas you said and all in here say has something missing...
    – Joao
    Commented Jan 17, 2018 at 15:49
0

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: enter image description here

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))
0
TEXTJOIN("_", TRUE, TEXTSPLIT("...", {"\";"/";":";"*";"?";"""";"<";">";"|";"."}))
1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jun 7, 2023 at 6:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .