An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Hi Andy,Starting with the 6.80R in a character field called [MyField], for example. Create a numeric calculated field and try the following:If(Right([MyField],1) = "R",Val([MyField]) * -1,Val([MyField]))Regards,Steve.------------------------------Steve CaielsProfessional ServicesAltair-------------------------------------------------------------------------Original Message:Sent: 10-02-2019 12:04 PMFrom: Andy ColesSubject: String to NumericHi allI have a file that's got a column of numbers stored as strings. Where a number is negative, rather than it showing in the usual way, it's shown with an "R" (for refund) after the numerals - so 6.80R for example. I've been trying to create a calculated field that'll manipulate those strings so that I can then convert the whole column to numbers, including negatives, rather than characters but I'm not getting anywhere. Does anyone have any suggestions please?ThanksAndy------------------------------Andy ColesPension ManagerDorset Healthcare University NHS Foundation Trust01305 361302------------------------------"
Thanks Steve!I'd completely discounted Val as an option as I thought the R would throw it out. That's really helpful.------------------------------Andy ColesPension ManagerDorset Healthcare University NHS Foundation Trust01305 361302-------------------------------------------------------------------------Original Message:Sent: 10-03-2019 03:13 AMFrom: Steve CaielsSubject: String to NumericHi Andy,Starting with the 6.80R in a character field called [MyField], for example. Create a numeric calculated field and try the following:If(Right([MyField],1) = "R",Val([MyField]) * -1,Val([MyField]))Regards,Steve.------------------------------Steve CaielsProfessional ServicesAltair------------------------------Original Message:Sent: 10-02-2019 12:04 PMFrom: Andy ColesSubject: String to NumericHi allI have a file that's got a column of numbers stored as strings. Where a number is negative, rather than it showing in the usual way, it's shown with an "R" (for refund) after the numerals - so 6.80R for example. I've been trying to create a calculated field that'll manipulate those strings so that I can then convert the whole column to numbers, including negatives, rather than characters but I'm not getting anywhere. Does anyone have any suggestions please?ThanksAndy------------------------------Andy ColesPension ManagerDorset Healthcare University NHS Foundation Trust01305 361302------------------------------"
