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!

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

maxient

try this user defined function

Please Login or Register  to view this content.

the wine must flow

and for numbers use

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

where A1 contains original string

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


Still getting use to 2007

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.

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

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

maxient can you please mark you post solved

thanks

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

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

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