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
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
Hi Mynda,
Thank you for your very quick response....but i need a fix without using power query. Is there a formula that i can use to convert everytime i have to use a similar report....which is quite often?
The date format im fine with.
Thank you
Grace
You can use this formula:
=VALUE(MID(B3,2,LEN(B3)-2))
Mynda
