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

Excel formula help

Options
  • 18-02-2014 2:27pm
    #1
    Ste-
    Registered Users Posts: 4,751 ✭✭✭


    Hi,

    trying to get a formula to work using MID and find commands.
    Just can't seem to get it.
    What I have is 2 strings say:

    A1
    XX000012345678V02
    B1
    XX001234567892V02

    Say I wanted to get the strings of numbers not including zeros
    So I want to extract 12345678 from A1 and 1234567892 from B2.
    Can't get the right formula using MID and FIND because of the different length in strings. I've tried using Right and counting in from V02 but still means I have leading zeros on B1 which I don't want.

    Thanks.

    Ste-.


Comments

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


    =1*RIGHT(LEFT(A1,LEN(A1)-3),LEN(A1)-5)


  • Options
    #3
    Ste-
    Registered Users Posts: 4,751 ✭✭✭Ste-


    Works perfectly thank you.


  • Options
    #4
    bnt
    Registered Users Posts: 13,005 ✭✭✭✭bnt


    You can use MID as well -- then get the numeric value of what's left, which strips of the extra zeroes in front.

    =VALUE(MID(A1,3,12))
    gives 12345678 and 1234567892 as results.

    From out there on the moon, international politics look so petty. You want to grab a politician by the scruff of the neck and drag him a quarter of a million miles out and say, ‘Look at that, you son of a bitch’.

    — Edgar Mitchell, Apollo 14 Astronaut



Advertisement