+ Reply to Thread
Results 1 to 15 of 15

how to remove first x digits and last y digits from a cell?

  1. #1
    Registered User
    Join Date
    09-25-2009
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    14

    how to remove first x digits and last y digits from a cell?

    Hi!
    I am new to this forum, I hope I am complying with the rules.
    Many thanks for reading this and for your welcome help.

    Now, let's go to the core of the trouble:

    I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell. Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
    Is there a way to remove this vintage year form the string?
    to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.

    examples are like below:
    De Wetshof Finesse/Lesca Cahrdonnay ‘07
    De Wetshof Sauvignon Blanc ‘07
    Lord Neethling Cabernet Franc 2002
    Lord Neethling Pinotage ‘01
    Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
    Jacobsdal Pinotage 1994
    Zondernaam Sauvignon Blanc 2007
    Tokara Red
    1976 St Emilion
    03 Tokara rose
    Plasir de Merle Cabernet Sauvignon ‘05
    DuToitskloof Pinotage/Merlot/Ruby Cabernet
    1999 Tradition Juracon 375ml



    I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.
    I have had some luck with the left side, as in:
    =IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))

    As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).

    I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!

    Does anyone have an idea on how to help with this?
    Ideally I would love to cut the vintage year, whether 2 or 4 digit, whether on right or left of cell and paste it in another cell, so to avoid manually doing it.
    However, this is surely too complicated to do, so iwould settle with just deleting the vintage year and manually typing the vintage in another cell.


    I have started deleting the vintage years by hand but it has proven far tooooooooooooooooooooo long as there are hundreds of spreasheets and tens of thousands of lines.

    Many thanks to you all!
    Last edited by maixent; 09-27-2009 at 11:32 PM. Reason: I have included examples when there is no vintage year or volume that must not be deleted.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,417

    Re: how to remove first x digits and last y digits from a cell?

    Hi maixent,

    Welcome to the forum.

    Have a look at the attached where I use jindon's custom Usfer Defined Function (udf) from here to provide a possible solution.

    Though it may still need some tidying up (i.e. removal of single quote marks) it's a long way there.

    Regards,

    Robert
    Attached Files Attached Files
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    maxient

    try this user defined function

    Please Login or Register  to view this content.
    the wine must flow

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    and for numbers use
    Please Login or Register  to view this content.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: how to remove first x digits and last y digits from a cell?

    Based on your samples, if you wanted to use a formula:

    Please Login or Register  to view this content.
    where A1 contains original string

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    DonkeyOte
    The extra nested formulas allowed in excel 2007 come in handy; this formulae is a keeper


    Still getting use to 2007

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: how to remove first x digits and last y digits from a cell?

    The formula should work across all versions (not 2007 specific).

    I can't take credit for the difficult bit - ie determining the number in the string via the LOOKUP this I picked up from Richard Schollar (MS Excel MVP) ... it is flawed if the string ever contains a valid number (not vintage)...

    The use of INDIRECT also makes this Volatile, you could revert to INDEX instead of INDIRECT:

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 09-25-2009 at 03:08 AM.

  8. #8
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    strange I cant get it to work in a 2007 converted to 2003 workbook
    I'll have to delve a little deeper with 2007 and 2003 compatibility

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: how to remove first x digits and last y digits from a cell?

    no you're right... I wasn't looking in a 2003 file... the TRIM takes it over the edge.

  10. #10
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    maxient can you please mark you post solved

    thanks

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: how to remove first x digits and last y digits from a cell?

    For a formula solution....

    With data in B2 this formula will give the year in D2 (if there is one)

    =IF(ISERR(LEFT(B2)+0),IF(ISERR(RIGHT(B2)+0),"",TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",99)),99))),LEFT(B2,FIND(" ",B2)-1))

    and then for the rest of the data in C2

    =TRIM(SUBSTITUTE(B2,D2,"",1))

  12. #12
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    daddylongleggs do you know it a type of spider in the west island of NZ
    any who ... another keeper

  13. #13
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: how to remove first x digits and last y digits from a cell?

    dont worry about that ! a 1976 St Emilion
    1976 Chateau Ausone Saint Emilion
    96 parker points not bad at all send any spare bottles to
    martindwilson@ blah blah blah
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  14. #14
    Registered User
    Join Date
    09-25-2009
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: how to remove first x digits and last y digits from a cell?

    thanks for the help, guys ^_^
    Sorry for not replying earlier, I was offline this weekend.
    I am going to try the offered solutions, starting with the long TRIM function, as it is something I can do, unlike the VBA, not having a clue how to make it work, oups....

    thanks again.
    Menawhile, if you have any other ideas, feel free to share your genius with me

    Now, where is the corscrew?????!

  15. #15
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: how to remove first x digits and last y digits from a cell?

    I have a spare where do you live and I'll deliver

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1