I have a list of cells that I'm searching for the value: !5-10 , and I want to replace all of them (100+) with the value 5-10, essentially just dropping the "!". When I use Find and Replace to do this en masse, it finds the target value just fine, but it changes it to "41769", even when the cell format is set to text.

Incidentally, if I do it manually and delete the "!" in each cell, it shows up as desired.

Why won't it do this automatically and how can I fix that?

Thanks,
mb

Hi Michael,

Am Tue, 28 Oct 2014 06:45:22 -0700 (PDT) schrieb michaelberrier:

Incidentally, if I do it manually and delete the "!" in each cell, it shows up as desired.

search for "!" and replace it with "'"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Tuesday, October 28, 2014 9:50:26 AM UTC-4, Claus Busch wrote:
Hi Michael,

Am Tue, 28 Oct 2014 06:45:22 -0700 (PDT) schrieb michaelberrier:

Incidentally, if I do it manually and delete the "!" in each cell, it shows up as desired.

search for "!" and replace it with "'"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus,
This originally started when I imported a workbook of survey data with a column representing years of experience. Some of the options were "1-3", 3-5", etc. For every one of those that could be translated into a date (1/3/2014), the spreadsheet automatically did that. Is there a way to stop Excel from doing that so I don't have to make this fix in the first place?

Hi Michael,

Am Tue, 28 Oct 2014 08:32:37 -0700 (PDT) schrieb michaelberrier:

This originally started when I imported a workbook of survey data with a column representing years of experience. Some of the options were "1-3", 3-5", etc. For every one of those that could be translated into a date (1/3/2014), the spreadsheet automatically did that. Is there a way to stop Excel from doing that so I don't have to make this fix in the first place?

if you format all cells before deleting the "!" the value should remain.
The "'" is only a prefix for text formatted cells. You only see it in
the edit bar. If you see it in the cell you have a leading space.
Then search " !" and replace with "'"

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Tue, 28 Oct 2014 08:32:37 -0700 (PDT) schrieb michaelberrier:

This originally started when I imported a workbook of survey data with a column representing years of experience. Some of the options were "1-3", 3-5", etc. For every one of those that could be translated into a date (1/3/2014), the spreadsheet automatically did that. Is there a way to stop Excel from doing that so I don't have to make this fix in the first place?

format the expected column as text before importing the data.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional