Converting Text Services to Counts of Services

[Mike S.]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Mike S.]

I've pursued this line already. The first try was on General. My next
attempt started with formatting the entire spreadsheet to Text BEFORE
changing any text to 1's, yet Excel still converted 1/1 to 1-Jan, etc. It
was after several variations of this that I evolved my current process.

I do see that my cell content descriptions were somewhat vague, so (as an
example of the problem):

Initially> B3=SUPP, C3=PHN, D3=SUPPX2/PHN, E3=SUPP/PHN, F3=SUPPX3/PHNX2
So I set the entire worksheet's format to text.

Now, I replace SUPP with 1.
So> B3=1, C3=PHN, D3=1X2/PHN, E3=1/PHN, F3=1X3/PHNX2

Then I replace PHN with 1.
So> B3=1, C3=1, D3=1X2/1, E3=1-Jan*, F3=1X3/1X2
* 1/1/07 in the fx box; per your statement, it should still be 1/1

Next, I replace 1X2 with 2.
So> B3=1, C3=1, D3=1-Feb*, E3=1-Jan, F3=1X3/2
* 2/1/07 in the fx box; per your statement, it should still be 2/1

Last, I replace 1X3 with 3.
So> B3=1, C3=1, D3=1-Feb, E3=1-Jan, F3=2-Mar*
* 3/2/07 in the fx box; per your statement, it should still be 3/2

The cells with dates are now formatted Custom (d-mmm), while I wanted them
to remain text. Do you now see my problem?

It makes it impossible to convert replace 1/1 with 2.

Any solutions?

 

[Sandy Mann]

Yes I see what you are saying - if you use the Edit > Replace option Excel
*helpfully* reformats the cell for you'

The answer is to precede all entries with a single apostrophe. The
apostrophe will not show but it will tell Bill Gates that you want it to
remain as text

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Mike S.]

So, for example, replace Phn with 'Phn and Supp with 'Supp. Cool idea.
Thank you very much.