Log In
Register
Home
May 23, 2020
Offline
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
Offline
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
May 23, 2020
Offline
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
1 Guest(s)
