String to Numeric

Andy_21788
Andy_21788 Altair Community Member
edited October 2019 in Community Q&A
​​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
------------------------------
Tagged:

Best Answer

  • Steve Caiels_21881
    Steve Caiels_21881
    Altair Employee
    edited October 2019 Answer ✓
    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
    ------------------------------"

Answers

  • Steve Caiels_21881
    Steve Caiels_21881
    Altair Employee
    edited October 2019 Answer ✓
    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
    ------------------------------"
  • Andy_21788
    Andy_21788 Altair Community Member
    edited October 2019
    Steve Caiels_21881 said:

    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
    ------------------------------"
  • Steve Caiels_21881
    Steve Caiels_21881
    Altair Employee
    edited October 2019
    Andy_21788 said:

    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
    ------------------------------"

Categories