locked
Extract specific data from long field RRS feed

  • Question

  • Hello,

    Does anyone know how (no VB code) to create two separate fields ("Class" and "Amount") in Access from the following import file:

    Fee   (Amount: 39.00 USD, Class: 07 Smog Update)Total: 39.00

    Fee   (Amount: 9.00 USD, Class: 08 Meet the Publisher)Total: 9.00

    Example:            CLASS                        AMOUNT

                  07 Smog Update                     39.00

                  08 Meet the Publisher               9.00

    Thank you


    John Fletcher


    • Edited by SpaceData Friday, May 20, 2016 4:06 AM
    Friday, May 20, 2016 4:03 AM

Answers

  • Let's say the field is named Text0. For Class, use the expression

    Mid([Text0],InStr([Text0],"Class:")+7,InStr([Text0],")")-InStr([Text0],"Class:")-7)

    And for Amount:

    Val(Mid([Text0],InStr([Text0],"Total:")+7))

    Apply the desired format to the Amount.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by ryguy72 Friday, May 20, 2016 12:04 PM
    • Marked as answer by SpaceData Friday, May 20, 2016 5:37 PM
    Friday, May 20, 2016 6:02 AM

All replies

  • Let's say the field is named Text0. For Class, use the expression

    Mid([Text0],InStr([Text0],"Class:")+7,InStr([Text0],")")-InStr([Text0],"Class:")-7)

    And for Amount:

    Val(Mid([Text0],InStr([Text0],"Total:")+7))

    Apply the desired format to the Amount.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by ryguy72 Friday, May 20, 2016 12:04 PM
    • Marked as answer by SpaceData Friday, May 20, 2016 5:37 PM
    Friday, May 20, 2016 6:02 AM
  • Hans you are awesome. Works perfectly. Thanks.

    John Fletcher

    Friday, May 20, 2016 5:38 PM