Forum Discussion
Applying editing to imported data
Hi all,
I'm importing data from a third party excel worksheet to my own database. The source unfortunately have already edited their data to display negative numbers in red having removed the - sign.
Is it possible that I can continue to import the data and for any red numbers to automatically be identified as negative and numbers in black as positive? I don't need to display the negative sign but I would need to be able to identify in this situation that the total gross positive was 216.8 (sumif(range,">0")) and the net was -97.2 (sum(range)) and also to be able to sort positive to negative.
Any help would be appreciated.
Hello, attached is an example using VBA. It works for the color red.
It is going to do an "if the color is red" then change the sign to negative else "leave the value as it is".
4 Replies
- if this is coming from excel those numbers are most likely just formatted that way and the actual VALUE is still a negative number. Hence if you just change the number format you can have it shown as black with "-" sign or inside brackets [ neg number ] or any way you wish. Look at Home tab, number formats
Unfortunately not. I should qualify and say that this data has been pasted onto an email from a source file. I can copy and paste the cells and it retains the color but no other way to identify positive or negative other than colour.
Hello, attached is an example using VBA. It works for the color red.
It is going to do an "if the color is red" then change the sign to negative else "leave the value as it is".
