I need to fix these cursed dates which can’t be sorted properly.
The “how to solve” is already given. Either find/replace with regular expression enabled, or text-to-columns. See the resources linked by @robleyd and @EarnestAl.
The Why:
- The apostrophe is a way to visually signal that something which looks numeric is not actually stored in numeric form, but rather as verbatim text.
- When you enter content with a valid numeric interpretation, which you want to have stored verbatim (not in numerical form), you can likewise signal this by typing a leading apostrophe.
The apostrophe in this context is not - and when typed it does not become - part of cell content. This is the reason why search will not find it.
3 Likes
Since you did not enter the dates manually, you should import them correctly, which is by far easier than repairing wrong data.
- In the text import dialog, you have to choose the right import language. If the text 7/9/2022 refers to 9th of July, choose English(USA), choose any other English in case of 7th of September.
- Always check option “detect special numbers”.
Both settings are preserved for the next import.
2 Likes
You have possibly failed to check Detect special numbers (or similar) when importing data. If so, you could re-import using the correct setting.
Otherwise, see for example Calc Conversion TEXT to DATE