How do I find or trim an inconsistent string of characters in a line item?

wld002
wld002
in Modeling

I am currently importing data with a concatenation of 3 General Ledger identifiers and a customer code. A sample would look like LE10000.LMF10000.1000000 for the first GL indentifiers. Everything is consistent and All LEs are 7 digits, all LMFs are 8 digites and all account codes are 8 digits. I have customer codes that are inconsistent in the number of characted that range from 4 characters to 12 characters. If my list item is LEXXXXX.LMFXXXXX.XXXXXXXX.Customer Code how do I trim off every character after the period after the accounting code? For reference purposes, LE, LMF, ACCT and Customer are each its own list so we need to parse them out for lookups and reporting once the data is imported.

Tagged:

Best Answer

  • pyrypeura
    pyrypeura
    Answer ✓

    Hi,

    with logics below you can extract codes of separate items so and then you can use then FINDITEM to get exact items to be used with lookups.

    image.png
    image.png

Answers

  • HimanshuRaj
    HimanshuRaj

    Hi @wld002

    You can use this formula to extract out your customer code

    RIGHT(Line item name, LEN(Line Item name) - 26)

    Breakdown of 26:

    7+8+8 for your ledger codes count and 3 for "." periods. Since 26 is fixed , you'll get your customer codes with the above formula

    "Line item name" is line item where you are uploading your concatenated data

    Cheers!!

Categories