replacing text in a cell
 

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

replacing text in a cell
 

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.
--
Kevin Backmann


"Roger Converse" wrote:

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

replacing text in a cell
 

When you're looking to work on *'s or ?'s, you have to precede them with an
tilde (~).

So in Find What, enter
~*

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Converse" wrote in message
...
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

replacing text in a cell
 

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

"RagDyer" wrote:

When you're looking to work on *'s or ?'s, you have to precede them with an
tilde (~).

So in Find What, enter
~*

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Converse" wrote in message
...
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

replacing text in a cell
 

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

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

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Converse" wrote in message
...
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

"RagDyer" wrote:

When you're looking to work on *'s or ?'s, you have to precede them with
an
tilde (~).

So in Find What, enter
~*

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Converse" wrote in
message
...
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