Find/Replace text, NOT format!!!

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

--
Message posted from http://www.ExcelForum.com

0
6/21/2004 5:11:44 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
313 Views

Similar Articles

[PageSpeed] 8

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

>-----Original Message-----
>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 and
>are stored 
>as 38082 (the number corresponding to April 5th, 2004)
>I changed the format of the column to "text".  This 
caused all of the
>"5-Apr" entries
>to change to "38082".  So now the whole column is in 
text format and I
>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 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.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
6/21/2004 6:10:51 PM
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

HennyBogan < wrote:

> 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 and
> are stored 
> as 38082 (the number corresponding to April 5th, 2004)
> I changed the format of the column to "text".  This caused all of the
> "5-Apr" entries
> to change to "38082".  So now the whole column is in text format and I
> 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 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.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
0
timotero (63)
6/21/2004 6:23:03 PM
"HennyBogan <" wrote...
...
>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.

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

--
To top-post is human, to bottom-post and snip is sublime.
0
hrlngrv (1990)
6/21/2004 7:10:58 PM
Harlan,

You're a pretty funny guy.

tim

Harlan Grove wrote:

> "HennyBogan <" wrote...
> ..
> 
>>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.
> 
> 
> 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*.
> 
> --
> To top-post is human, to bottom-post and snip is sublime.
0
timotero (63)
6/21/2004 7:20:34 PM
Heck!

Don't encourage him!

Al

--
Message posted from http://www.ExcelForum.com

0
6/21/2004 7:45:17 PM
Reply:

Similar Artilces:

Conditional formatting
Is it possible to shade fields progressively darker or lighter depending on the values. For example, I would like the following values to have the following colours: -5 dark red -4 lighter red -3 slightly lighter red -2 etc, etc 1 light green 5 dark green One step further - would it be possible to tell excel to work out which is the lowest and highest number in the data set and work out for itself how light or dark to shade the values e.g, if my dataset happened to have the values -50, -40, etc excel would work out which was a low value and which was a high one. Conditional formatting ...

Loading PivotCache directly from a text file
According to the Excel online help, the PivotCache.Connection property can be set to "the path to and file name of a text file". I tried setting it to the path and file name of a .csv file and it returned an error. Is there a way to load a PivotCache directly from a flat file without having to load into a spreadsheet or database? (The flat file has more than 1 million rows.) -- Tom Sherwood ...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Creating a chart with text not numbers
How do I create a chart that has text values (freshman, sophomore, junior, senior) instead of numbers (1, 2, 3, 4)? I have a column labeled "What is your class standing?" and below it is 166 rows where people selected "Freshman," "Sophomore," etc. I want to create a bar chart that counts how many people selected each of the four classes. Thanks, Alan First of all, you will have numbers to plot. The words are your category labels. Read how to turn a column of words like this into a histogram: http://peltiertech.com/Excel/Charts/Histograms.html - Jon...

Formatting four pages onto one 12x18 sheet
I'm trying to figure out how to format a 12 x 18 sheet with four individual 5.5 x 8.5 pages on it. Any ideas? The closest I can get is under page setup...selecting "postcards" and that will give me three pages per sheet, but I need four. Any ideas or thoughts are greatly appreciated. If your printer can be custom setup to 12 x 18 then you can divide it into four parts. Are you sure you aren't trying to setup a 11 x 17 landscaped sheet? In any event, setup your printer first. File, Page Setup, Advanced on the right, type 18 width, 12 height or 17 width, 11 ...

Find both matched and unmatched entries
Hi, I have two tables that I wish to find: 1) find matched data - copy that into a separate table 2) find unmatched data - copy that data into a separate table Can I achieve the above results with one query? I have three fields in each table that I would like to compare against: Client Data Status Any assistance with this would be greatly appreciated... Cheers, GLT Hi Yes you can use an append query to do this quite simple BUT you would end up with the same data in 2 tables which be a mistake. Maybe you could post some more details of what you are trying to do (the end result) a...

How do I format a field based on the value
I have a form that is populated from a query using several tables. In the detail section I have the following fields: Category Code qty PO number ... .... ... The qty is populated in the query based on whether or not a PO number is available. If the PO number is not available then it comes from table a, if it is available then it comes from table b. On the form, I have my fields color-coded for easy reading. I would like to be able to color the border and column heading based on where this value comes from. If table A, then it is blue, if table b then it...

Extend text to next cell
I am trying to get my text to run over to the next cell only in display not actual value of that cell. So if A1 says, "How now brown cow.", it should show over cells A2 and A3. However, I also need the carriage returns to be picked up. If I use the "Wrap Text" property it only shows the sentence in that cell. If I don't use it I get the [] (brackets) in place of the carriage return. Maybe you could merge those cells (A1:A3 or did you really mean A1:C1?) But be aware that merging cells causes trouble with sorting, pasting, and lots of other stuff. Format|cells|al...

Display text in Excel Bubble chart. #2
H1!, I have an Excel spreadsheet which contains X-coordinates, Y-Cordinates, Well name and Cumulative Oil/Gas/Water production. I have created bubble map using X,Y and Z(Cum production). I need to show the well name on each Bubble, which I am unable to do. Kindly sugest method to display the well name on the bubbles. Thanks, Raja Mukherjee Raja - Rob Bovey's Chart Labeler (http://appspro.com) is a free addin that adds text from the worksheet to data labels on a chart series. I just tested it on a bubble chart and it works fine. - Jon ------- Jon Peltier, Microsoft Excel MVP ...

Formatting number in Data Table
I am using Excel 2007. I have a spreadsheet that the figures can be negative or positve. I have formatted the cells with this custom format $#,##0;[Red]$-#,##0. On the spreadsheet the format works fine. I created a chart from the data. I have a data table at the bottom of the chart. The problem is that the format from the spreadsheet is not reflected in the data table. When I click on Format Data Table, there is no option for number formatting. There is a number formatting option available when i click Format Axis, but this doesn't change it, because the actual axis is Text. Is th...

Format Painter button problem
When I wish to format several cells the same I double click the format painter and I am able to format each cell without reclicking the button. I have just upgraded to Excel 2003 and now I can only do that on a worksheet that hasn't any VBA code attached. Is this a bug? I know other people on Mr Excel are having the same problem and no one seems to have an answer. Thanks for any response or help you may give me. Skip Depends upon the VBA code attached, I would imagine. If you have worksheet event code that formatted cells, this formatting would overwrite what you painted with the...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

how to find all matches in an array
I have a task list that in one column lists the person primarily responsible for it and in the 2nd column is the back up person. The 3rd column is the task. On another sheet I want to put in someones name and bring up all tasks they are responsible for and all the tasks they are backup for. What is an easy way to do this. Easiest way IMHO would be to use advanced filter http://www.contextures.com/xladvfilter01.html -- Regards, Peo Sjoblom (No private emails please) "Greg" <Greg@discussions.microsoft.com> wrote in message news:FA4CB596-6451-4569-81AF-24EC18C50180...

Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...

Justifying text of an axis
How do you justify the text of an axis in an excel chart? On Mon, 21 Apr 2008, in microsoft.public.excel.charting, mdicke <mdicke@discussions.microsoft.com> said: >How do you justify the text of an axis in an excel chart? You can't, it's fixed by the axis. The best you can do is select a monospaced font and use a custom number format with ???? (question marks). -- Del Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. ...

Trouble with time formats
Any help appreciated! I need to work out the average speed it would take to cover a certain distance. EG. If you covered 10 miles in 28 minutes what would teh average speed be... Column A: 10 miles Column B: 28 minutes Column C: Would return 21.43mph. I then want to to add time increments of say 15 seconds in rows so that I can see that if it took 28 minutes, 45 seconds, the average speed column would return 20.87mph. I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s), but I want to format it as time, i.e: 00:28:45 (or 01:10:30) Is this possible? Thanks...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Imported text file w/o unique identifiers, find duplicates
Imported a txt file w/o unique identifiers and have run a query to find possible duplicate records based on 3 seperate fields. How can I either update those fields that may be in error in the original table or append a new record w/o the duplicate data and delete the original record? In the following I need to determine which records to keep, which to delete and/or which to update. End Date Total Sheets Time Operator Name 3/3/2007 14 0:00 00094123 B D MCNARA 3/3/2007 14 0:00 00095681 NAME UNKNOWN 3/17/2007 84 0:01 00091234 C D S 3/17/2007 84 0:01 000...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

Need RULE to search for Text in Body with varying # of spaces
Hi there, I have Outlook 2002 and have been using the outlook rule condition: "With specific words in the body" However the "Specific words" I'm looking for might contain varying number of spaces: EX: "some text: some more text" "some text: some more text" Is there a wildcard or something to match multiple spaces? --Thanks very much, Kenneth Kenneth Parker <kenneth.parker@sas.com> wrote: > I have Outlook 2002 and have been using the outlook rule condition: > "With specific words in the body" > > However...

Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two conditions for green, without using two conditions for green is there a way to use OR somehow to beat the limit? Thanks, Norm PS Win2000 + XL2002 Sure ... just click on "Formula Is", and you can use "OR" for more then two conditions. For example: =OR(A1=2,A1=4,A1="good",A1="bad") Will trigger the set format if "any" of the above equate to "True". -- HTH, RD --------------------------------------------------------------------------- Please keep all corr...

text font color won't print in Excel, why?
I am using Excel 2002. I changed the text font color of the cell. It displays correctly on my screen, but it prints as black text even though I selected a pale yellow. Word 2002 prints correctly. The pale yellow prints as a light shade of gray. Does anyone know why? Thanks eagle Perhaps in Page Setup>Sheet you have "Black and White" checked? Gord Dibben Excel MVP On Sun, 23 Jan 2005 22:51:01 -0800, eagle7 <eagle7@discussions.microsoft.com> wrote: >I am using Excel 2002. I changed the text font color of the cell. It >displays correctly on my screen, b...

Conditional Formatting for Image box on Continuous Forms
Hello again, This problem is driving me crazy. I am a car enthusiast and love taking photos of classic cars. I have a table which describes the make and model in a series of fields. There are several fields that contain a reference to photos of those cars. Ie: the fields are called "Front" "Side" "Rear" "Angle". My form has four image boxes to display the images stored in these fields. But when I open the form all the records show the images of the first car only. I asked about this a few days ago and was advised to check out http://www.lebans.co...

date Format #28
-- Regards, ------------------------------------------------- Mahmoud Metwally Ali Jaicorp IT Manager Office Tel. : (202) 3471495/361 Mob. : (2010) 5195169 ...

Importing text files into Excel
Does anyone know of a quick and easy way to bring large text files into Excel. The data in the text files is separated by spaces. The spaces that separate the data vary in number. I have tried to replace the spaces with tabs while in Notepad but it takes too long. Any help would be appreciated! Thanks Chris Open Excel and create a blank workbook. Then: Data | Get External Data | Import Data (this command might be something like Import Text File, if you are using an older version of Excel). Browse to find your file and click to Import it. Should open up the Text Import Wizard. Make...