

May 23, 2020

Good day Mynda, its me again...sad face
I download reports from Oracle Fusion a lot and the date format as well as 'number' is always giving trouble to use with Vlookup for other data.
Can you place a formula in the 2 columns highlighted in orange to clean and correct my data please.
Thanks much
Grace


July 16, 2010

Hi Grace,
The date format is easy, just format it with a custom number format dd-mmm-yy.
The Number column is more complicated. There is a non printing character before and after the number you see in the cell. If you =LEN(B6) you'll see it returns 8, but there are only 6 numbers visible in the cell.
I loaded the table to Power Query and split the column by number of characters, once by 1 from the beginning and once by 1 from the end and then I was able to convert the data type to whole number and then delete the columns split out that contain the non-printing characters. See file attached.
Mynda
1 Guest(s)
