Keeping text formatted as text when replacing

[Frank Marousek]

I have a column of data which consists of entries such as:

Decimal Reamer .0300

I am trying to remove the words "Decimal Reamer" from each cell in the
column. To do this, I am doing the following:

1. Formatt the entire column as Text.

2. Select the entire column.

3. Press Cntrl-H.

4. Enter "Decimal Reamer " to be replaced with "" (nothing).

5. Click Replace All.

When I do this, the text is removed as desired. However, the remaining text
is re-formatted as a number (dropping the trailing zeroes). I do not want
this. How can I make it stop?

 

[dvt]

Decimal Reamer .0300 [snip]
Enter "Decimal Reamer " to be replaced with "" (nothing).

When I do this, the text is removed as desired. However, the
remaining text is re-formatted as a number (dropping the trailing
zeroes). I do not want this. How can I make it stop?

You can replace with "'" (double, single, then double quote). That will
leave the cell formatted as text. It also has other (possibly undesirable)
consequences, such as leaving the spaces after "Decimal Reamer" in the cell.

Or you could format the resulting cell to Number with 4 decimal places to
keep the trailing zeros.

Dave
dvt at psu dot edu

 

[J.E. McGimpsey]

One way:

Change your replace string to be an apostrophe (')

 

[Frank Marousek]

Thank you for your reply.

Unfortunately, your first suggestion leaves me with "'" (double quote,
single quote, double quote) at the beginning of each cell. Perhaps I did not
do it correctly? I have "Decimal Reamer " in the Find What box and "'" in
the Replace With box.

And your second suggestion will not work because a) not all of the numbers
are 4-place decimals, and b) I am going to be adding additional text to the
numeric portion (so I need to keep it formatted as text).

Frank

Decimal Reamer .0300 [snip]
Enter "Decimal Reamer " to be replaced with "" (nothing).

When I do this, the text is removed as desired. However, the
remaining text is re-formatted as a number (dropping the trailing
zeroes). I do not want this. How can I make it stop?

You can replace with "'" (double, single, then double quote). That will
leave the cell formatted as text. It also has other (possibly undesirable)
consequences, such as leaving the spaces after "Decimal Reamer" in the cell.

Or you could format the resulting cell to Number with 4 decimal places to
keep the trailing zeros.

Dave
dvt at psu dot edu