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

Excel formula help

  • 18-02-2014 1:27pm
    #1
    Ste-
    Registered Users, Registered Users 2 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

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


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


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


    Works perfectly thank you.


  • #4
    bnt
    Registered Users, Registered Users 2 Posts: 13,074 ✭✭✭✭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.

    You are the type of what the age is searching for, and what it is afraid it has found. I am so glad that you have never done anything, never carved a statue, or painted a picture, or produced anything outside of yourself! Life has been your art. You have set yourself to music. Your days are your sonnets.

    ―Oscar Wilde predicting Social Media, in The Picture of Dorian Gray



Advertisement