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

1. ## 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.

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&#37; 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

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!

2. ## 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

3. ## 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. ## 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. ## 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. ## 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. ## 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.``

8. ## 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. ## 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. ## Re: how to remove first x digits and last y digits from a cell?

maxient can you please mark you post solved

thanks

11. ## 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. ## 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. ## 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

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. ## 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

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

#### 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