Stop autoformatted dates without using "format TEXT" or '
 

First the problem- I'm trying to modify a large database with fields such as this "+12/+7/+2" by removing +'s (which are unnecessary for my purposes) and leaving the /'s. The 'Find and Replace' function should do this just fine- except it won't. Whenever I remove the +'s, what's left becomes a date. The cells start off formatted as text, but as soon as I 'find and replace' anything, they become date fields.

How in the heck can I get Excel to stop doing this?!?!?!

I've been googling advice on this subject for almost an hour. Almost all the similar solutions to my problem have included- "just format the cells as TEXT and it should work just fine" or "add an apostrophe(or a space) before your numbers and that should do it."

My problem with those "solutions" are twofold- 1) my cells supposedly ARE formatted as text, but apparently Find and Replace can ignore that and change it to whatever it wants, and 2) why should I have to add in unnecessary characters just to trick Excel into not using a "feature" I don't want it to use in the first place, especially since my whole point in doing this is to get rid of some unecessary characters!

So, is there a way to stop Find and Replace from applying the format it "thinks" you want to use instead of the one you actually told it to use?

A permanent solution to this problem would be greatly appreciated- and by more than just me I'm sure.
-Dave

Stop autoformatted dates without using "format TEXT" or '
 

For lack of a better solution, you can use Data TextToColumns and use +
as the delimiter setting all columns to TEXT and eliminating the import of
the first blank one..........then CONCATENATING the three columns back
together........if you have to do it frequently, it can be set to a
macro.......

Vaya con Dios,
Chuck, CABGx3



"DavidB" wrote:


First the problem- I'm trying to modify a large database with fields
such as this "+12/+7/+2" by removing +'s (which are unnecessary for my
purposes) and leaving the /'s. The 'Find and Replace' function should
do this just fine- except it won't. Whenever I remove the +'s, what's
left becomes a date. The cells start off formatted as text, but as soon
as I 'find and replace' anything, they become date fields.

How in the heck can I get Excel to stop doing this?!?!?!

I've been googling advice on this subject for almost an hour. Almost
all the similar solutions to my problem have included- "just format the
cells as TEXT and it should work just fine" or "add an apostrophe(or a
space) before your numbers and that should do it."

My problem with those "solutions" are twofold- 1) my cells supposedly
ARE formatted as text, but apparently Find and Replace can ignore that
and change it to whatever it wants, and 2) why should I have to add in
unnecessary characters just to trick Excel into not using a "feature" I
don't want it to use in the first place, especially since my whole point
in doing this is to get rid of some unecessary characters!

So, is there a way to stop Find and Replace from applying the format it
"thinks" you want to use instead of the one you actually told it to
use?

A permanent solution to this problem would be greatly appreciated- and
by more than just me I'm sure.
-Dave


--
DavidB

Stop autoformatted dates without using "format TEXT" or '
 

Just thought of another way..............first replace all / with //, then
replace all + with nothing, then replace all // with /..............

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

For lack of a better solution, you can use Data TextToColumns and use +
as the delimiter setting all columns to TEXT and eliminating the import of
the first blank one..........then CONCATENATING the three columns back
together........if you have to do it frequently, it can be set to a
macro.......

Vaya con Dios,
Chuck, CABGx3



"DavidB" wrote:


First the problem- I'm trying to modify a large database with fields
such as this "+12/+7/+2" by removing +'s (which are unnecessary for my
purposes) and leaving the /'s. The 'Find and Replace' function should
do this just fine- except it won't. Whenever I remove the +'s, what's
left becomes a date. The cells start off formatted as text, but as soon
as I 'find and replace' anything, they become date fields.

How in the heck can I get Excel to stop doing this?!?!?!

I've been googling advice on this subject for almost an hour. Almost
all the similar solutions to my problem have included- "just format the
cells as TEXT and it should work just fine" or "add an apostrophe(or a
space) before your numbers and that should do it."

My problem with those "solutions" are twofold- 1) my cells supposedly
ARE formatted as text, but apparently Find and Replace can ignore that
and change it to whatever it wants, and 2) why should I have to add in
unnecessary characters just to trick Excel into not using a "feature" I
don't want it to use in the first place, especially since my whole point
in doing this is to get rid of some unecessary characters!

So, is there a way to stop Find and Replace from applying the format it
"thinks" you want to use instead of the one you actually told it to
use?

A permanent solution to this problem would be greatly appreciated- and
by more than just me I'm sure.
-Dave


--
DavidB