replacing text in a cell

  • Thread starter Thread starter Roger Converse
  • Start date Start date
R

Roger Converse

Hello,

I have a file with about 5,000 records. A majority of them are fine, but
some of them have an * in the cell. This prevents my query from matching up
with another file, because there should not be a * in the cell.

so for example one file contains this product id:

FRK F218026*

and the other file contains

FRK F218026

I have tried to do a replace, but when I replace the * everything
disappears. I next tried to filter on anything that contains an *, but that
didn't filter anything. Apparently MS Excel "thinks" that there is an * in
every cell.

Any suggestions would be greatly appreciated.

Thank you,
Roger
 
Assuming that the asterisk is always the last character...

Insert a helper column to the right of the product ID and enter the
following formula, changing cell addresses as needed:

=IF(RIGHT(A1,1)="*",LEFT(A1,LEN(A1)-1),A1)

Copy down the column as far as necessary.

Copy the entire contents of the helper column, move to the first cell in the
product ID column and click EDIT in the menu, select PASTE SPECIAL and then
click the VALUES option button. Click OK to complete.

Delete the helper column when done.
 
When you're looking to work on *'s or ?'s, you have to precede them with an
tilde (~).

So in Find What, enter
~*
 
Thank you both for the reply. I tried the ~* method and it worked perfectly.

I will keep that other formula handy. I am sure I can find uses for that as
well.

Thank you,
Roger
 
You're welcome, and thank you for the feed-back.

If you like using formulas, you can try this one:

=SUBSTITUTE(A1,"*","")
 
Back
Top