Mike,

I don't see your previous posts but to stop 1/1 from becoming 1-Jan etc. you
must format the cell BEFORE you make the entry. After the entry has been
made it makes no difference to what is actualy stored in the cell. The
reason why you get 39083 is because that is what has actually been staored
in the cell. Try entering 1/1 and then re-formatting the cell to General
you will again get 39083. (If you check it before reformatting you will see
that the formatting has changed to Custom "dd-mmm") The reason for the
number is that is a count of the number of days since 1/1/1900. Excel
"thinks that by entering 1/1 you are entering a date and, as there is no
year given it assumes that you mean this year and so inters the number for
January 1st 2007. The formatting does not change what is in the cell it
just makes it look different.

If the cell is formatted as Text first before any entry it will remain
exactly as you enter it.
Another way of entering text is to enter an apostrophe first like '1/1 the
apostrophe will not show but Excel will know that the entry is text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Mike S." wrote in message
...
(First, I'm VBA ignorant.)

I've been working on converting text strings like "SUPPX2", "PHNX3", and
"SUPP/PHNX2" into counts of services; per my examples: 2, 3, 3.

Each time I've used #s as replacements, I keep getting dates in cells that
were formatted as Text only. As described in previous posts, "1/1"
becomes
"1-Jan" and "2/3" becomes "2-Mar". When I reformat the cells to Text
Only,
the new dates become meaningless numbers. (I'm guessing Julian numbers
for
the specified dates, but I don't read Julian and don't like having to
guess
which # corresponds to the "1/1" that I wanted.) This results in my
having
to undo all the work I've done, just to get back to the original.

As a result, I've been forced to locate textual replacements that wouldn't
be mistaken as numbers or Excel-recognized symbols. Example: Replacing
"SUPP"
with "-" makes "SUPPX3" into "-X3" which Excel sees as a reference to cell
X3. (Don't know why, grrr.) Changing "PHN/SUPPX2" by the same method
results
in cell errors referring to invalid formulas. (double grrr.)

I finally found that "+" either singly, or in conjunction with other "+"s
or
symbols (letters), does not prompt Excel to either reformat the cell or
register an error, but this leaves me with the following process: I use
the
following replacements (= is used in place of "is replaced with" & "" is a
blank): "INTAKE"=""; "PHONE"="+"; "SUPP"="+"; "+X2"="++"; "+X3"="+++";
"+X4"="++++"; "+X5"="+++++"; "+/+"="++"; "+++++++++"="9"; "++++++++"="8";
"+++++++"="7"; "++++++"="6"; "+++++"="5"; "++++"="4"; "+++"="3";
"++"="2"; "+"="1".

Is there a way to force Text Only cell formatting to be maintained despite
the presence of a number pattern or number in the cell?

(I've read other posts on this issue, but I'm wondering if anything new
has
been done to rectify this problem. For Microsoft: Why not exempt the Text
Only cell format from parsing or date corrections?)

Addendum: A coworker is tracking the info, and I want to avoid increasing
that person's workload.

--
Mike S.