none
exclude characters from a text string RRS feed

  • Question

  • Good morning all.

    I'm looking to exclude a series of characters after a specific character in a string of characters. 

    I have posted on Answers, but there's not as much activity there as there is here. 

    http://answers.microsoft.com/en-us/office/forum/office_2010-excel/check-for-letter-and-only-show-numbers-after-that/eee24ee7-8c58-4582-9793-a5ed156405cf

    I have a string (352 of them), which is made up of 2 letters--- X, and Y--- with a set of numbers following each letter. These numbers are coordinates in an X/Y coordinate system. 

    I want to focus on JUST the X coordinates in one column, and JUST the Y coordinates in another column. One user provided a working solution for the Y column, so I now need the X values. 

    The problem that I'm running up against is that their solution for Y counts/includes the Y's coordinates when applied to X. 

    This does not work for my purposes.

    So, my present situation is---

    What would it require to exclude all characters from Y forward? 

    TYIA.

    Best.

    Tuesday, October 27, 2015 4:08 PM

Answers

  • For X:

    =TRIM(MID(N5,2,FIND("Y",N5)-2))

    or if you want to return a numeric value:

    =1*TRIM(MID(N5,2,FIND("Y",N5)-2))


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

    • Marked as answer by SteveDB1 Tuesday, October 27, 2015 5:38 PM
    Tuesday, October 27, 2015 5:30 PM

All replies

  • For X:

    =TRIM(MID(N5,2,FIND("Y",N5)-2))

    or if you want to return a numeric value:

    =1*TRIM(MID(N5,2,FIND("Y",N5)-2))


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

    • Marked as answer by SteveDB1 Tuesday, October 27, 2015 5:38 PM
    Tuesday, October 27, 2015 5:30 PM
  • For X:

    =TRIM(MID(N5,2,FIND("Y",N5)-2))

    or if you want to return a numeric value:

    =1*TRIM(MID(N5,2,FIND("Y",N5)-2))


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

    Bingo!

    Thank you Hans.

    Have a  great day!!!!!

    Tuesday, October 27, 2015 5:38 PM