I need to compare the value of a cell to the values in a range of cells.
Problem is, these alpha-numeric values may have spaces and/or dashes, which
need to be ignored. Example- ab-uty should find abuty, a buty, and a-b uty as
duplicates. I have tried to use the substititute function to clear the
dashes and spaces, which works well for the compare cell, but will not work
on the range. Am I outta luck here?

Create a helper column for the range containing the same functions as for the
compare cell and compare the cell to this helper range!

Regards,
Stefi

€žWiz€� ezt Ã*rta:

I need to compare the value of a cell to the values in a range of cells.
Problem is, these alpha-numeric values may have spaces and/or dashes, which
need to be ignored. Example- ab-uty should find abuty, a buty, and a-b uty as
duplicates. I have tried to use the substititute function to clear the
dashes and spaces, which works well for the compare cell, but will not work
on the range. Am I outta luck here?

I have never heard of a "helper column" and nothing comes up in Excel help
when I search for the term. Could you explain further?

Thanks,
Wiz

"Stefi" wrote:

Create a helper column for the range containing the same functions as for the
compare cell and compare the cell to this helper range!

Regards,
Stefi

€žWiz€� ezt Ã*rta:

I need to compare the value of a cell to the values in a range of cells.
Problem is, these alpha-numeric values may have spaces and/or dashes, which
need to be ignored. Example- ab-uty should find abuty, a buty, and a-b uty as
duplicates. I have tried to use the substititute function to clear the
dashes and spaces, which works well for the compare cell, but will not work
on the range. Am I outta luck here?

A helper column is simply a normal column which is not used therefore can
contain auxiliary formulae. With your example:

If column A
cell A1: abuty
cell A2: a buty
cell A3: a-b uty

then enter in column B (helper column)
cell B1: =SUBSTITUTE(TRIM(A1),"-","")
drag it down to B3 (or as requested in real circumstances)

let C1 be the compare cell:
ab-uty
then this formula in D1 (dragged down to D3)
=B1=SUBSTITUTE(TRIM($C$1),"-","")
will return TRUE in each row.

As you can see it compares $C$1 to "helper" range B1:B3 instead the original
A1:A3.
Of course if you simply want to compare cells, you can use also this format
=SUBSTITUTE(TRIM(A1),"-","")=SUBSTITUTE(TRIM($C$1),"-","")
and in this case you don't need a helper column.

Regards,
Stefi

€žWiz€� ezt Ã*rta:

I have never heard of a "helper column" and nothing comes up in Excel help
when I search for the term. Could you explain further?

Thanks,
Wiz

"Stefi" wrote:

Create a helper column for the range containing the same functions as for the
compare cell and compare the cell to this helper range!

Regards,
Stefi

€žWiz€� ezt Ã*rta:

I need to compare the value of a cell to the values in a range of cells.
Problem is, these alpha-numeric values may have spaces and/or dashes, which
need to be ignored. Example- ab-uty should find abuty, a buty, and a-b uty as
duplicates. I have tried to use the substititute function to clear the
dashes and spaces, which works well for the compare cell, but will not work
on the range. Am I outta luck here?