Naming cells goes wrong!

%3CLINGO-SUB%20id%3D%22lingo-sub-1888339%22%20slang%3D%22en-US%22%3ENaming%20cells%20goes%20wrong!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EI%20have%20a%20file%20recording%20magazine%20issues.%26nbsp%3B%20Each%20block%20of%2012%20(1per%20year)%20contains%20the%20year%20number%26nbsp%3B(such%20as%201992)%20in%20column1%20(General%20format%20-%20changing%20it%20to%20Date%20format%20won't%20work%2C%20and%20changing%26nbsp%3Bit%20to%20Number%20format%20makes%20no%20difference)%20and%20the%26nbsp%3Bmonth%20name%20in%20column%202%2C%20viz%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22300%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2230px%22%3EJan%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2257px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2257px%22%3EFeb%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2257px%22%3EJill%20Bauman%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2257px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2257px%22%3EMar%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2257px%22%3ERon%20Walotsky%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2230px%22%3EApr%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2257px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2257px%22%3EMay%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2257px%22%3EDavid%20Hardy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2230px%22%3EJun%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2257px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2257px%22%3EJul%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2257px%22%3EBryn%20Barnard%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%22218px%22%20height%3D%2230px%22%3EAug%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%22218px%22%20height%3D%2230px%22%3ESept%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2230px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2230px%22%3ENov%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2281px%22%20height%3D%2230px%22%3E1992%3C%2FTD%3E%3CTD%20width%3D%2274px%22%20height%3D%2230px%22%3EDec%3C%2FTD%3E%3CTD%20width%3D%22144px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20wish%20to%20name%20column1%20row%201%20%22Jan92%22%2C%20and%20row%2012%26nbsp%3B%20%22Dec92%22%26nbsp%3B%20(exact%20spelling).%26nbsp%3B%20So%20I%20select%20that%20cell%2C%20which%26nbsp%3Bhighlights%26nbsp%3Band%20its%20location%20(A1)%20is%20displayed%20in%20the%20upper%20left%20space).%26nbsp%3B%20I%20go%20to%20that%26nbsp%3Bspace%20and%26nbsp%3Bovertype%26nbsp%3Bthe%20%22A1%22%20which%20appears%20there%20with%20%22Jan92%22%20(exact%20spelling).%20The%20name%26nbsp%3Bis%20not%20accepted%20-%20it%20is%20converted%20into%26nbsp%3Ball%20uppercase%20letters%20(JAN92)%20and%20the%20display%20cycles%20to%20highlight%20Column%20JAN%2C%20row%2092%20%2C%20ie%20a%20location%20in%20the%20file%20(even%26nbsp%3Bthough%20the%20file%20is%20nowhere%20near%20that%20wide).%26nbsp%3B%20I%20have%20discovered%20that%20I%20can%20overcome%20this%20by%20preceding%20the%20name%20with%20an%20Underscore%20(_)%20character%2C%20but%20this%20strikes%26nbsp%3Bme%20as%20unnecessarily%20tedious%20and%20a%20bit%20of%20a%20cop-out%20by%20the%20developers%20of%20Excel.%26nbsp%3B%20Aside%20from%20that%20though%2C%20I%20have%20to%20commend%20those%20same%20developers%26nbsp%3Bfor%20what%20is%20truly%20an%20awesome%20and%20incredible%20piece%20of%20software.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1888339%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888360%22%20slang%3D%22de-DE%22%3ESubject%3A%20Naming%20cells%20goes%20wrong!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888360%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411100%22%20target%3D%22_blank%22%3E%40ChrisgrZA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%3C%2FP%3E%3CP%3EKnowledge%20of%20Excel%20version%20and%20the%20operating%20system%20is%20a%20must%20have%20if%20you%20want%20to%20proposing%20a%20reasonable%20solution%20(Example%3A%20office%20version%20e.g.%202016%20or%202019%20or%20365%20web%20or%20365%20pro%2C%20etc.%20and%20your%20operating%20system%20(e.g.%20Win10%20(2004)%2C%20Win%2010%20(1903)%2C%20Mac%2C%20etc.).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a file recording magazine issues.  Each block of 12 (1per year) contains the year number (such as 1992) in column1 (General format - changing it to Date format won't work, and changing it to Number format makes no difference) and the month name in column 2, viz:

 

1992Jan 
1992FebJill Bauman
1992MarRon Walotsky
1992Apr 
1992MayDavid Hardy
1992Jun 
1992JulBryn Barnard
1992Aug
1992Sept
1992Oct 
1992Nov 
1992Dec 

 

I wish to name column1 row 1 "Jan92", and row 12  "Dec92"  (exact spelling).  So I select that cell, which highlights and its location (A1) is displayed in the upper left space).  I go to that space and overtype the "A1" which appears there with "Jan92" (exact spelling). The name is not accepted - it is converted into all uppercase letters (JAN92) and the display cycles to highlight Column JAN, row 92 , ie a location in the file (even though the file is nowhere near that wide).  I have discovered that I can overcome this by preceding the name with an Underscore (_) character, but this strikes me as unnecessarily tedious and a bit of a cop-out by the developers of Excel.  Aside from that though, I have to commend those same developers for what is truly an awesome and incredible piece of software.

 

2 Replies

@ChrisgrZA 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.

Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

 

Thank you for your understanding and patience

 

 

Nikolino

 

I know I don't know anything (Socrates)

@ChrisgrZA 

Excel automatically converts texts like Jan92 into the dates (Jan 01, 1992 - you may check in formula bar). In general it is always to work with dates using desired format for them (e.g. "mmmyy").

 

If you still prefer texts instead of dates you may apply Text format to the cells before you add values into them; or enter texts with single apostrophe in front like 'Jan92, such texts wont be converted and apostrophe won't be visible.