Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Red face Need formula to extract from string

    Need some help

    How can I create a formula that will extract the last 4 characters ( in this case numbers) of a string which may also contain a number of spaces before the last 4 characters.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    Alrom,

    Try this: =Right({your string or reference here},4)
    ExcelRight.JPG
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,089
    Thanks
    39
    Thanked 190 Times in 177 Posts
    The Geek's formula is correct. If by chance there are spaces within the last 4 characters but you want to exclude them then add the trim command:
    =Right(trim({your string or reference here}),4)
    This will remove all spaces from the text prior to evaluating it with the "Right" command

    HTH

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    Maudibe,

    Sorry trim only removes spaces at the beginning & ending of a string.
    It would however strip spaces from the trailing end.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,051
    Thanks
    11
    Thanked 35 Times in 34 Posts
    Maybe get rid of all the spaces before selecting the right 4: =right(substitute(A1," ",""),4)

  7. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,089
    Thanks
    39
    Thanked 190 Times in 177 Posts
    RGeek,
    Correct u r ....thx

  8. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for your help so far. I have been playing around with your suggestion but still have issues.

    Here is an example of the type of string I am working with "John P. Doe 1945" in cell A1. I want to end up with only "1945" as a number in cell A3 and "John P. Doe" in cell A2. If someone adds a space at the end of the string, part of the number "1" , "19" if two spaces, appear in A2 and missing from A3. Help.
    Last edited by alrom; 2012-11-26 at 10:32.

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    in A2:
    =MID(TRIM(A1),1+FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),LEN(A1))

    in A3:
    =LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

    Steve

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    Alrom,

    Ok, I think this is what you want.
    In Col B: =TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-4)))
    In Col C: =RIGHT(TRIM(A2),4)
    RIghtTrim.JPG
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #10
    Star Lounger
    Join Date
    Oct 2012
    Posts
    51
    Thanks
    1
    Thanked 7 Times in 7 Posts
    Hi alrom

    Another idea maybe of some help or not!

    Based on RetiredGeek sheet.

    In C2: =--RIGHT(SUBSTITUTE(A2," ",""),4)

    in B2: =TRIM(SUBSTITUTE(A2,C2,""))
    Last edited by Kevin@Radstock; 2012-11-30 at 03:14. Reason: Change cell reference to correspond with RetiredGeek picture.

  12. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I would like to attach a thumbnail pic of my problem but if I do a screen print and save it as a jpeg file and attach it turns out so small it is impossible to see. RGeek, how do you create your thumbnail?

    If you can read this attachment, I need a space in E4 between Doe and 67
    Attached Images Attached Images
    Last edited by alrom; 2012-11-29 at 12:54.

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,960
    Thanks
    193
    Thanked 730 Times in 666 Posts
    Alrom,

    Your attachment is entirely readable if you just click on it to enlarge it.
    I usually use the Snipping Tool in Win 7/8 and save the file as a .jpg and then attach it.

    Without the error check the formula would be =CONCATENATE(B2," ",D2)
    You don't need 2 concatenate functions since you can have many arguments to one.

    BTW: concatenate will NOT generate an error if one or all of the arguments returns a blank it will just return a blank.You may want something along this line if I read your code example correctly:
    =IF(ISBLANK(D3),CONCATENATE(B3," ",C3),CONCATENATE(B3," ",D3))
    concatenate.JPG
    Last edited by RetiredGeek; 2012-11-29 at 13:42.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  14. The Following User Says Thank You to RetiredGeek For This Useful Post:

    alrom (2012-11-29)

  15. #13
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks RG, and I thought I knew everything about win7. BTW if D3 is a blank then I will except it. the idea is the return the persons age based on the year in C3, if the year is missing, so be it.

    Thanks again.

  16. #14
    Star Lounger
    Join Date
    Oct 2012
    Posts
    51
    Thanks
    1
    Thanked 7 Times in 7 Posts
    Hi alrom

    Another option could be!

    Names in cells A3:A5 and the date 2012 in B1 (Current year)
    In B3 and copy down:

    =TRIM(LEFT(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3&12 34567890))-1))&" "&$B$1-RIGHT(A3,4)

    Or if you still require as your picture:

    In B3: =TRIM(SUBSTITUTE(A3,C3,""))
    In C3: =--RIGHT(SUBSTITUTE(A3," ",""),4) Note include the double unary.
    In D3: =$B$1-C3
    In E3: =B3&" "&D3

    Kevin
    Last edited by Kevin@Radstock; 2012-12-01 at 00:57.

  17. #15
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Retired Geek, Is there any advantage to using the Concatenate function over something like =A2 & " " & A3?

Page 1 of 2 12 LastLast

Posting Permissions

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