Find and Replace

[JHL]

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.

 

[Kevin B]

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.

 

[JHL]

Thanks Kevin.
I've encountered a second problem with this imported file. When I type your
formula, the cell maintains the formula and does not perform the action on
cell A1.
When I originally download the file off the mainframe, I save it as text,
and after making changes I save it as and Excel file. But there must be
something more I need to do in order for this formula to work.

Can you help with this? Thanks.