String to Numeric

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 Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 10-02-2019 12:04 PM
From: Andy Coles
Subject: String to Numeric

Hi all

I 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?

Thanks

Andy


------------------------------
Andy Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 361302
------------------------------"

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 Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 10-02-2019 12:04 PM
From: Andy Coles
Subject: String to Numeric

Hi all

I 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?

Thanks

Andy


------------------------------
Andy Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 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 Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 361302
------------------------------
-------------------------------------------
Original Message:
Sent: 10-03-2019 03:13 AM
From: Steve Caiels
Subject: String to Numeric

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 Caiels
Professional Services
Altair
------------------------------

Original Message:
Sent: 10-02-2019 12:04 PM
From: Andy Coles
Subject: String to Numeric

Hi all

I 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?

Thanks

Andy


------------------------------
Andy Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 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 Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 361302
------------------------------
-------------------------------------------
Original Message:
Sent: 10-03-2019 03:13 AM
From: Steve Caiels
Subject: String to Numeric

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 Caiels
Professional Services
Altair
------------------------------

Original Message:
Sent: 10-02-2019 12:04 PM
From: Andy Coles
Subject: String to Numeric

Hi all

I 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?

Thanks

Andy


------------------------------
Andy Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 361302
------------------------------"

Hi Andy,

It will take what it can, until it reaches the first non numeric character.

123R45 = 123
R123 = 0

Regards,
Steve.

------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 10-03-2019 05:09 AM
From: Andy Coles
Subject: String to Numeric

Thanks Steve!

I'd completely discounted Val as an option as I thought the R would throw it out.  That's really helpful.


------------------------------
Andy Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 361302
------------------------------

Original Message:
Sent: 10-03-2019 03:13 AM
From: Steve Caiels
Subject: String to Numeric

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 Caiels
Professional Services
Altair

Original Message:
Sent: 10-02-2019 12:04 PM
From: Andy Coles
Subject: String to Numeric

Hi all

I 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?

Thanks

Andy


------------------------------
Andy Coles
Pension Manager
Dorset Healthcare University NHS Foundation Trust
01305 361302
------------------------------"