I am cleaning data as below. The raw data column has multiple formats: text, number, date, custom so it’s hard to convert all of them at the same time. Can you help me pls?
| Raw date | Raw format | Target date | Target format |
|---|---|---|---|
| 20051231 | Text | 20051231 | Number |
| 20051231 | Number | 20051231 | Number |
| 31-Dec-05 | Custom/number | 20051231 | Number |
| 2005-12-31 | Date | 20051231 | Number |
=IF(ISTEXT(A12),VALUE(TEXT(A12,"YYYYMMDD")),IF(ISNONTEXT(A12),A12,VALUE(A12)))
New contributor
minh tue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.