none
Correct syntax for =MID(B1,(RIGHT(B1,10)),1) RRS feed

  • Question

  • Was hoping to be able to use something like this

    =MID(B1,(RIGHT(B1,10)),1)

    to get all characters left of the last 11 characters in the cells when the number of characters vary. Also there are no spaces in the string. example strings; (G110.129.1.53) or (G2510.129.1.54) without the ().

    As a side note I can get the =RIGHT(B1,11) formula to get the characters at the end but I can't figure out the syntax to get the left 3, 4, 5 or 6 characters as the case may be.

    Thank you for your help!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, May 12, 2017 2:07 PM

Answers

  • Try

    =LEFT(B1,LEN(B1)-11)

    If there is a chance that B1 has fewer than 11 characters, use

    =LEFT(B1,MAX(LEN(B1)-11,0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Friday, May 12, 2017 4:49 PM
    Friday, May 12, 2017 3:44 PM

All replies

  • Try

    =LEFT(B1,LEN(B1)-11)

    If there is a chance that B1 has fewer than 11 characters, use

    =LEFT(B1,MAX(LEN(B1)-11,0))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Friday, May 12, 2017 4:49 PM
    Friday, May 12, 2017 3:44 PM
  • Thanks Hans,

    Seems like I should have been able to see that but was stuck in my own thinking.

    =LEFT(B1,LEN(B1)-11)

    Will always work in this instance. Never less than this number of characters.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, May 12, 2017 4:51 PM