Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

MS Excel Formula

  • 30-07-2012 10:17am
    #1
    Jageranimus
    Registered Users, Registered Users 2 Posts: 11 ✭


    Hello,

    I am currently stuck on a formula in excel, here is an outline of my problem:
    I have a column in excel containing strings of data such as:

    XXX88888888
    XXX11111111
    XXX000/00/88888888
    XXX000/00/11111111

    I need to get rid of "000/00/" in front of the numbers and I am using the following formula to do so, you will notice that i need to add "XXX" in front as well.

    My Formula:
    =IF(SEARCH("/",D2)<>7,D2,"XXX"&RIGHT(D2,LEN(D2)-SEARCH("/",D2)-3))

    The issue is that if SEARCH("/",D2)<>7, then D2 is not displayed, it says #VALUE! instead, but if the false part outputs the correct answer such as XXX88888888
    Tagged:


Comments

  • #2
    PrzemoF
    Registered Users, Registered Users 2 Posts: 1,931 ✭✭✭PrzemoF


    If the head/tail is fixed lenght you can consider using only left/right functions. 
    =left(d2,3) & right (D2,8)
    It's bulletproof, but the input data has to be 100% consistent.


  • #3
    Jageranimus
    Registered Users, Registered Users 2 Posts: 11 ✭Jageranimus


    Excellent, thanks.
    Much simpler solution, ended up with ="XXX"&RIGHT(D2,8)


  • #4
    Jageranimus
    Registered Users, Registered Users 2 Posts: 11 ✭Jageranimus


    And just realized why I went for the long solution in the 1st place, the date is not consistent, some of the entries are smth like 00-888888881111111, they exceed 8 characters, that's the reason for trimming everything before and including "/", data with slashes is consistent, but i need to get rid of the "000/00/" bit.


  • #5
    fionny
    Registered Users, Registered Users 2 Posts: 2,367 ✭✭✭fionny


    I'd just do a find and replace on it personally,


  • #6
    Jageranimus
    Registered Users, Registered Users 2 Posts: 11 ✭Jageranimus


    Got it, thanks for your help, formula at the end is as follows:

    =IF(FIND("/",D2&"/",1)=7,"XXX"&RIGHT(D2,8),D2)


  • Advertisement
Advertisement