General Discussions

 View Only
Back to discussions
Expand all | Collapse all

Separating the Account number and Alias from Standard Account Display in Vena

  • 1.  Separating the Account number and Alias from Standard Account Display in Vena

    Posted 06-13-2023 16:55

    Hi Everyone

    I just wanted to share something that many of you may come across: 

    The standard way that Vena displays accounts is by showing both account numbers and alias in brackets together.  Most of you may have had this situation where you would like to separate the Account Number and Alias.  Reasons may include just a different way of reporting and displaying the accounts.  You may just want the description of the account or just the account number for lookups.

    Suppose we have a form with the below Accounts:

    Separating Accounts

    If the Accounts are in column G of the form. To get the Account Number from the Account, we can add a column to the right of G and use the below using FIND function in combination with text parsing functions (LEFT, RIGHT, and LEN):

    =RIGHT(LEFT(G26,FIND(" (",G26,1)-1),LEN(LEFT(G26,FIND(" (",G26,1)-1)))<o:p></o:p>

    Once the formula works for one Account, you can simply copy and paste the formula in the rows below to view only the Account Numbers.

    For example, if the Account is '3006 (Sales A)', the above formula will fetch the Account Number 3006. Below screenshot shows how we have got the Account Number using the above formula.

    Separating Alias

    If the Accounts are in column G of the form. To get the Alias from the Account, we can add a column to the right of G and use the below similar function that finds the text between the brackets:

    =MID(G26,FIND("(",G26)+1,FIND(")",G26)-FIND("(",G26)-1)

    The above formula will fetch the Alias from between the two parentheses.

    Once the formula works for one Account, you can simply copy and paste the formula in the rows below to view only the Alias for all the other Accounts.

    For example, if the Account is '3006 (Sales A)', the above formula will fetch the Alias 'Sales A'. Below screenshot shows how we have got the Alias using the above formula.

    Thanks Everyone

    #GiveAndGetHelp #LetsGrow


    ------------------------------
    Mark Dose
    Delbridge Solutions
    mdose@delbridge.solutions
    ------------------------------



    ------------------------------
    Mark Dose
    Delbridge Solutions
    mdose@delbridge.solutions
    ------------------------------