Preserving Text Only despite Excel's # Recognition Processes

[Mike S.]

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

Re:

(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/com...3-c2773cddbba6&dg=microsoft.public.excel.misc

 

[Wood Grafing]

When I need to have Excel ignore a number in a text field, I precede the
number with the ' character. Forces Excel to ignore the numbers and treat
them as text, and doesn't display in the actual field with the ', just in the
formula bar.

HTH, at least it's worth trying.

Wood


***

 

[Mike S.]

So, does this mean that I need to replace "SUPP" with '1, repeating the
process with "PHN" and the "X2"s, "X3"s, and "X4"s? Or am I getting the
process confused?
(So SUPP/PHN becomes '1/'1, and SUPPx4/PHNX3 becomes '4/'3...)

Thanks for your assistance.

 

[Wood Grafing]

You only need the ' at the beginning of the cell, not for each entry. The '
forces excel to treat everything after it as text.

 

[Mike S.]

Thanks,

Your advice has helped.

 

[Pete_UK]

Another approach, rather than using Find & Replace, is to use a
formula which makes use of several SUBSTITUTE functions - this will
return text. The formula can be copied down the column as required,
and then the values can be fixed in place.

Hope this helps.

Pete