Results 1 to 10 of 10

Thread: text extract

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    text extract

    hi all,

    what would be the formula if you want to extract a variable length text located at the end of a string. for example the text can be "SE' or "wed" or "dawn".

    TIA
    Last edited by dubdub; 2012-05-23 at 03:12.
    TIA
    dubdub

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by dubdub View Post
    hi all,

    what would be the formula if you want to extract a variable length text located at the end of a string. for example the text can be "SE' or "wed" or "dawn".

    TIA
    does this text always start at the same point? if so, MID(text,start-point,255) will grab as many characters as it can from that start-point.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    the string has variable length and the text is always at the end of the string.

    TIA
    TIA
    dubdub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I presume the end substring is after a final space in the string. How about a formula like:
    =IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),A1,MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))+1,LEN(A1)))

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Assume i want to extract the exact text,"SE' or "wed" or "dawn", in the string and these text are at the end.

    TIA
    TIA
    dubdub

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    To extract x characters from the end of a text string, the formula is
    =RIGHT(string, x)

    So, if you know how many characters you want to extract, this formula is simple.
    If you want to know whether a particular string ends with 'SE', 'wed', or 'dawn', you could test for this with an IF(..) formula.

    zeddy

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Any suggestion(s) for a nested right function with if(..) for these specific text(s) in a string.

    TIA
    TIA
    dubdub

  8. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Any suggestion(s) for a nested right function with if(..) for these specific text(s) in a string.

    TIA
    TIA
    dubdub

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =IF(RIGHT(A1,2)="SE","SE",IF(RIGHT(A1,3)="wed","we d",IF(RIGHT(A1,4)="dawn","dawn","something else")))

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks to you all, and special Thanks to Steve.

    dubdub
    TIA
    dubdub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •