# Excel won't Listen!!! **URL:** \ **Category:** Factual Questions\ **Created:** [February 22, 2013, 3:30pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083 "2013-02-22T15:30:26Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 3:30pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/1 "2013-02-22T15:30:26Z")
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 ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [February 22, 2013, 3:46pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/2 "2013-02-22T15:46:26Z")
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. ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [February 22, 2013, 3:50pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/3 "2013-02-22T15:50:11Z")
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! ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 3:51pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/4 "2013-02-22T15:51:39Z")
Thanks Enright but I am unclear. Are you saying put in single tick when doing find/replace? ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [February 22, 2013, 3:57pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/5 "2013-02-22T15:57:25Z")
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. ---
**Author:** ![JerrySTL](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@JerrySTL](https://boards.straightdope.com/u/JerrySTL)\ **Post date:** [February 22, 2013, 4:00pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/6 "2013-02-22T16:00:16Z")
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. ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 4:03pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/7 "2013-02-22T16:03:33Z")
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 ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [February 22, 2013, 4:03pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/8 "2013-02-22T16:03:35Z")
> [@stackman1](#): > > Thanks Enright but I am unclear. > > Are you saying put in single tick when doing find/replace? 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. ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 4:05pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/9 "2013-02-22T16:05:28Z")
> [@JerrySTL](#): > > 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. Thanks Jerry (and I mean that) but why doesn’t excel LOCK in the text data type? ;)) ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 4:08pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/10 "2013-02-22T16:08:17Z")
> [@Enright3](#): > > 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. 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? ---
**Author:** ![Really\_Not\_All\_That\_Bright](https://avatars.discourse-cdn.com/v4/letter/r/e8c25b/32.png) [@Really\_Not\_All\_That\_Bright](https://boards.straightdope.com/u/Really_Not_All_That_Bright)\ **Post date:** [February 22, 2013, 4:11pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/11 "2013-02-22T16:11:22Z")
Can’t you just reformat the column after you finish moving data around? ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [February 22, 2013, 4:13pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/12 "2013-02-22T16:13:30Z")
> [@stackman1](#): > > 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. I see that he’s already expanded on that. > [@Enright3](#): > > 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. 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? ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 4:14pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/13 "2013-02-22T16:14:24Z")
Reformat where? ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 4:17pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/14 "2013-02-22T16:17:21Z")
> [@Johnny\_L.A](#): > > 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? Hey Johnny - get you’re own thread! (Just kidding - although I am still screwed as far as I can see…) ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [February 22, 2013, 4:23pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/15 "2013-02-22T16:23:58Z")
> [@stackman1](#): > > I then copy this column into an application program that expects Jun-2005 not 'Jun-2005 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. ---
**Author:** ![stackman1](https://avatars.discourse-cdn.com/v4/letter/s/94ad74/32.png) [@stackman1](https://boards.straightdope.com/u/stackman1)\ **Post date:** [February 22, 2013, 4:41pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/16 "2013-02-22T16:41:48Z")
> [@Enright3](#): > > 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. 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 🙂 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. ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [February 24, 2013, 4:48am UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/17 "2013-02-24T04:48:19Z")
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 ---
**Author:** ![Really\_Not\_All\_That\_Bright](https://avatars.discourse-cdn.com/v4/letter/r/e8c25b/32.png) [@Really\_Not\_All\_That\_Bright](https://boards.straightdope.com/u/Really_Not_All_That_Bright)\ **Post date:** [February 24, 2013, 5:00pm UTC](https://boards.straightdope.com/t/excel-wont-listen/651083/18 "2013-02-24T17:00:50Z")
> [@Really\_Not\_All\_That\_Bright](#): > > Can’t you just reformat the column after you finish moving data around? > [@stackman1](#): > > Reformat where? 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.