For Microsoft: Why not exempt the Text Only cell format from parsing or date
corrections?



(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 other 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 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.)

--
Mike S.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc