Hi,
I have downloaded a CSV file from my internet banking, and the date comes in the format 01Jan2011 which Numbers doesn't recognise as a date. I am wondering if it is possible to split these strings such that it would read 01 Jan 2011?
Thanks,
Nick
Macbook Pro, Mac OS X (10.7), 2.66 GHz Intel Core i7 8GB RAM
you can use the functions:
- LEN() to get the total length of the string
- LEFT() to get the left n characters of a string
- MID() to get the n cdharacters starting at posiiton m
- RIGHT() to the get the n right characters
Assuming that there are always two characters for the day, three for the month and four for the year:
you can use:
New Date = =LEFT(A2, 2)&" "&MID(A2, 3, 3)&" "&RIGHT(A2,4)
you can use the functions:
- LEN() to get the total length of the string
- LEFT() to get the left n characters of a string
- MID() to get the n cdharacters starting at posiiton m
- RIGHT() to the get the n right characters
Assuming that there are always two characters for the day, three for the month and four for the year:
you can use:
New Date = =LEFT(A2, 2)&" "&MID(A2, 3, 3)&" "&RIGHT(A2,4)
Thanks Wayne. That does the splitting of the string just fine, but I can't get Numbers to recognise the cells as dates. I have applied the date format '5 Jan 2009' to Column B, but when I try to sort this column by date, it is still recognised as a string.
Nick
I would do the following:
perform the operation I previously provided in another sheet (or table).
I would copy the updated dates and paste them back over the originals using the "Edit > Paste Values".
then I would format the original date column as a date using the formatter
Perfect! Thanks again.
Is it possible to split a date string in the form 01Jan2011?
