I receive a text file which I convert some columns to text and others to
general. Then I save the file with a €œ.xls€� extension.

One column that is saved as text has a character code 31 item then some
additional digits.
Example: 123456-A

I want to delete the special character and leave the rest. Using €œfind and
replace€� does not find the special character even when I type the code
char(31) in the find box. I notice in a completely new worksheet find and
replace will find these special characters, but I can't get the same result
using this converted text file.

Is this doable using find and replace?

Thank You.

Create a helper column to the right of the column with the character 31
prefix character and enter the following formula, changing the A1 cell
address to match the first cell in the column:

=RIGHT(A1,LEN(A1)-1)

Copy the formula down the column as far as necessary.

Copy the entire formula column and move to the first cell of column with the
character 31 prefix character and click EDIT, PASTE SPECIAL, click VALUES and
the click the OK command button.

Delete the helper column when done.
--
Kevin Backmann


"JHL" wrote:

I receive a text file which I convert some columns to text and others to
general. Then I save the file with a €œ.xls€� extension.

One column that is saved as text has a character code 31 item then some
additional digits.
Example: 123456-A

I want to delete the special character and leave the rest. Using €œfind and
replace€� does not find the special character even when I type the code
char(31) in the find box. I notice in a completely new worksheet find and
replace will find these special characters, but I can't get the same result
using this converted text file.

Is this doable using find and replace?

Thank You.