Find/Replace text, NOT format!!!

H

HennyBogan

HELLO.
I am running into an extremely irritating problem in excel 2003.
Here is the situation, I am dealing with files that are very large,
for example the file in question has 12,900 rows.
All of the information is imported from a text file, generated by
another program. I didn't originally bring in the information,
someone else did, and now I am in charge of this file.

So here is the situation, there is a column called "DISPOSITION"
containing 4 digit names. One of the names is "5APR"
When this list was imported some time ago, the format of the column
must have been "general" and excel interpretted "5APR" as the date
"April 5th".
Now, in this column, there are hundreds of entries that say 5-Apr an
are stored
as 38082 (the number corresponding to April 5th, 2004)
I changed the format of the column to "text". This caused all of th
"5-Apr" entries
to change to "38082". So now the whole column is in text format and
have a
few hundred "38082" entries that need to be changed to 5APR.
I then used find/replace to replace 38082 with 5APR for that column.
Here is the annoying part. Even though I had already changed th
number format
for the column into "TEXT" excel automatically changed it Back to dat
format
when it replaced the entries with 5APR. What the hell? I did not AS
excel to change the number format. It just assumed on its own. Also
I can do without all of the suggestions it makes. I have turned of
all autocorrect feature.
Anyway, I am not about to sift through 13 thousand rows, nor am I even
going to find them and replace hundreds of entries manually. Doe
anyone have
any suggestions, and why is excel doing this? Is this useful t
anyone? I mean
come on, I specifically changed the number format and excel took i
upon
itself to change it to what IT wanted when i used find/replace text.
This is
ridiculous
 
J

jeff

Hi,

Frustrating indeed.

You could try this macro to reset your values. It
assumes you want a DMMM (day number+3 characterMonth)
format in uppercase. adjust the range to suit your
data. (and try it on test data first)

Private Sub CommandButton1_Click()
Dim r As Range
Dim x As Long
Dim y As String
Set r = Range("C18:C27")

For Each c In r
c.Value = Format(c, "DMMM")
y = ""
For j = 1 To Len(c)
y = y & UCase(Mid(c, j, 1))
Next j
c.Offset(0, 0) = y
Next c

End Sub

Hope this gets you somewhere.
jeff
 
T

Tim Otero

Henny,

I would suggest, when you replace the entries, put an apostrophe before
the 5APR, so it would look like this '5APR. This will make the entry
text. Only thing is, it would also become part of the entry.

You could also put this in a helper cell and copy it down (assumes 38082
is in A1) =IF(A1=38082,"5APR",A1). Then, copy all the cells with the
formula and use Paste Special/Values to paste back over the original values.

tim
 
H

Harlan Grove

...
...
Here is the annoying part. Even though I had already changed the
number format for the column into "TEXT" excel automatically changed
it Back to date format when it replaced the entries with 5APR. What
the hell? I did not ASK excel to change the number format. It just
assumed on its own. Also, I can do without all of the suggestions it
makes. I have turned off all autocorrect feature.
Anyway, I am not about to sift through 13 thousand rows, nor am I even
going to find them and replace hundreds of entries manually. Does
anyone have any suggestions, and why is excel doing this? Is this useful
to anyone? I mean come on, I specifically changed the number format and
excel took it upon itself to change it to what IT wanted when i used
find/replace text. This is ridiculous.
Click to expand...

C'mon. Microsoft paid good money to one or two experienced Excel users and a few
hundred semicomotose dullards to use Excel in their usability labs, and the
stats showed that most of these users benefitted from this thoroughly behavior.
So get with the program - become a semicomotose dullard! Just not one too dull
to be able to write Microsoft checks for periodic upgrades bringing ever more
HELPFUL functionality to Excel!

Without sarcasm, the evidence is overwhelming that several people on the Excel
team have to be sadists. Otherwise they'd provide a way to turn this stuff off.

In this case, use Edit > Replace, replace 38082 with '5APR - the leading single
quote is *essential*.
 
T

Tim Otero

Harlan,

You're a pretty funny guy.

tim

Harlan said:
...
..



C'mon. Microsoft paid good money to one or two experienced Excel users and a few
hundred semicomotose dullards to use Excel in their usability labs, and the
stats showed that most of these users benefitted from this thoroughly behavior.
So get with the program - become a semicomotose dullard! Just not one too dull
to be able to write Microsoft checks for periodic upgrades bringing ever more
HELPFUL functionality to Excel!

Without sarcasm, the evidence is overwhelming that several people on the Excel
team have to be sadists. Otherwise they'd provide a way to turn this stuff off.

In this case, use Edit > Replace, replace 38082 with '5APR - the leading single
quote is *essential*.
Click to expand...
 
Top