Excel won't Listen!!!

Factual Questions
1

I am building a column of TEXT. I DEFINED the column as TEXT.

If I build a column of TEXT that has this kind of content Jan-2005 thru Dec-2005 but then try to do find/replace on 2005 (or -2005 for that matter) to 2006…Excel imposes its own will on my TEXT field and gives me Jan-06, Feb-06 etc

Why wont Excel just look for the 2005 string and change the string to 2006. Do I have to do something special in Advanced Options in Find/Replace? I told Excel to look at the data as text - doesn’t that tell Excel - TO LAY OFF???

Is it just me? Am I the only one frustrated at every turn by Excel - I’m no genius but I’m not a knucklewalker either (I think)?

Thanks Dopers

2

I hate excel for reasons just like this.
Instead of putting “Jan-2005” in the text field, start the field with a single quote mark:
'Jan-2005
I tested it and a global search & replace worked correctly.

3

Resistance is futile.

OP: It’s not you. I actually like Excel, overall, but there are times when it frustrates the hell out of me!

4

Thanks Enright but I am unclear.

Are you saying put in single tick when doing find/replace?

5

This is one of the many things that annoy me about Excel (and Access, and…). One thing I do is to put ‘01 - 30’ in a cell when I copy and paste a ‘map’ of where my data is. Excel changes it to ‘30-Jan’, much to my chagrin. Now when I cut and paste my ‘map’, I do this:
[ul][li]Define the column as Text;[/li][li]Paste as Text.[/ul][/li]I’ve just experimented with the example given in the OP, and by golly it does change the date format! I run across this myself when I want to change ‘000-000-0000’ to ‘0000000000’. In that case, I define the column as Text, sort on the column, type in the string of zeros, copy the cell, highlight all of the cells I want to change, and paste.

Enright3’s suggestion is much easier, and I’ll use it from now on.

But yeah… I hate that Excel assumes it knows what I really want better than I do.

6

If you are mostly storing a lot of data, consider moving over to Access. You can lock in the field’s data type as text, date/time, number, memo and a bunch of other types.

7

Johnny -

Can you expand on Enrights post. How/why do I use a tick mark? In find/replace function or in building cells? I am still lost.

Thanks
Peter

8

No. I’m saying change all of your values to have a leading quote (tick/apostrophe).

Any cell that starts with the single quote / apostrophe will always be regarded as text. I’ve read that it’s a throwback to support Lotus 1-2-3, where you could add leading characters into a cell to format it. By adding a leading apostrophe in a cell (permanently) will not show up in the field itself; but will keep the cell saved as a text field. It’s impervious to search and replace; which attempts to reformat the cell based on the data that it gets changed to.

9

Thanks Jerry (and I mean that) but why doesn’t excel LOCK in the text data type?

;))

10

Enright -
My problem is that once I populate my spreadsheet - I then copy this column into an application program that expects Jun-2005 not 'Jun-2005

Does that make sense? I was trying to use excel to make my life easier - silly me huh?

11

Can’t you just reformat the column after you finish moving data around?

12

I see that he’s already expanded on that.

There are some files I receive that have the workbook formatted this way. Mostly I find it to be a PITA, because I need to edit some cells and the apostrophe throws me off. (i.e., I want to change something at the beginning of the cell, and there’s that extra character I need to deal with.) In my case, there’s never a need for cells to be formatted that way. ‘123 Easy St’ is text whether or not it has an apostrophe. Can I just change all apostrophes to nothing and get rid of them? If so, that would get rid of apostrophes I actually want to keep. Is there another way to change the format of those cells?

13

Reformat where?

14

Hey Johnny - get you’re own thread! (Just kidding - although I am still screwed as far as I can see…)

15

The leading apostrophe is not visible in Excel. I write Easytrieve programs that require the input file to be fixed-position text. Due to limitations in the way Excel saves text files, I need to import the files into Access and export them with defined field positions. When I import a file that has the formatting apostrophes, these are not carried over into the Access file. It’s as if they are not there at all.

16

Enright -

Sorry I didn’t follow you at first! It does work!! Thank You

My God - is that written anywhere? Well Microsoft is pretty good about backward compatibility but Lotus 1-2-3 - c’mon on :slight_smile:

Of course that leads to THE question:

Why don’t they just program Excel to treat Text fields as Strings and not impose formatting that is not requested.

17

Hey if we ever figure out that one we can start working on why SQL Server imports excel files and sets data types by sampling the first few rows only. It’s maddening as hell to import numeric text data and it gets handled as integer.

Annoyance #2 with the import process into SQL server is that there is an implied wild card character on file extensions. I.e. if I say import .xls files it will also import xlsx files. The problem with that for me is that we use to rename files by adding junk characters to the extension as a quick & dirty way to skip a file from being Imported. Now we can’t do that because .xlsdjdjdkdkfkf is the same as .xls from that standpoint

18

If you highlight the column (or just the set of cells) and right-click, it will let you apply your preferred date format - xx/xx/xxxx, xxx/xx, or whatever.