# Remove First X Digits And Last Y Digits From A Cell

Sep 25, 2009
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!

View 14 Replies
ADVERTISEMENT
Sep 25, 2009

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

2003 Tokara Red

1976 St Emilion

03 Tokara rose

Plasir de Merle Cabernet Sauvignon 05

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.

View 9 Replies
View Related
Oct 31, 2008

i need to format my numbers in the following format

10,00,000.00

the first three digits will be separated and then subsequently 2 digits

View 2 Replies
View Related
Apr 30, 2014

I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.

=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)

View 12 Replies
View Related
Oct 8, 2009

1. Remove the first 4 digits from each "Appeal ID"

2. Insert a new column (first column) called "Chapter"

3. Run a v-lookup down the new column against a file that is stored on my desktop. The v-lookup will cross check the Appeal ID against the file to identify the Chapter

4. Sort the data alphabetically by Chapter

5. Create seperate Excel files for each Chapter ...

View 13 Replies
View Related
Jan 29, 2014

I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.

I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB.

View 12 Replies
View Related
Jul 9, 2009

I know how to remove the first part of a text, but not the end. I have a very large amount of data in which the first column contains a product description and then the UPC code. I need to isolate the product name from the UPC code into 2 columns. I used =RIGHT(B2,14) to isolate the UPC, but since the length of the product name varies, I'm not sure how to isolate the product name. I have looked at using the TRIM and TRUNC functions without any luck. 'Text to columns' doesn't work because I have multiple spaces, 'Text to columns' would work if I could used 2 digits as the delimiter, but the function doesn't allow that.

Can someone please suggest a function or formula for this?

Here is an examples of how the text appears in the field. (Note: I only need to cut off the UPC code, I need the measure information to stay).

some really long product name 12 oz 5 bag 09-99999-99999

another shorter name 2 oz 09-99999-99998

View 6 Replies
View Related
Nov 24, 2009

I was wondering how do you format a cell so that when i enter the number 7 it automatically sets it at 007 and for like 10 it would be 010 so a must have of 3 digits

View 5 Replies
View Related
Mar 9, 2014

adding the sum of digits in a cell using VBA. For eg: in A1 if I have 12345 I need the sum of 12345 (15) in cell B1.

View 3 Replies
View Related
Aug 18, 2014

I found a formula that would copy only the last 2 digits of a previous cell and put it in a new cell. For example below, I want the cells to the right of the below to be:

12345 45

26548 48

21854 54

211ae ae

I thought it was a =right or something.

View 1 Replies
View Related
Dec 9, 2009

These are source cells:

('sheet 1'!h14) 26.9% (246/914)

('sheet 1'!h15) 23.4% (314/914)

('sheet 2'!h14) 33.8% (212/577)

I want to insert ONLY the number after the left parentheses and before the slash into sheet 3 same cells and sheet 4 same cells

('sheet 3'!h14) 246

('sheet 3'!h15) 314

('sheet 4'!h14) 212

something like

='sheet 1'!h14(after (before /)...?

View 6 Replies
View Related
Feb 25, 2010

What type of formula do I need that will convert the following into one sum?

in Cell A1 : 34-23

I need a formula that will add 34+23 into another cell... this should be simple, but I've never had to write one for this before...

View 12 Replies
View Related
Jun 4, 2014

I have pasted over 1300 numbers in a list, all of which need to have 5 digits. However, when I pasted the 5 digit number (ie 00003) into the cell, it omits the leading 0's.

Is there any way to get these numbers back to having 5 digits....and if it omitted the 0's, can I put them back all in one fell swoop?

View 3 Replies
View Related
Jun 10, 2014

find a formula or function where i can remove the last 4 digits from cells. I have a spreadsheet where there was might have been concatenation do where 4 digits were added to the end of part numbers in a cell. I need to remove just the last 4 digits so that I can do matches later on. Each cell has a different number of characters and i just need to remove the last for Example 154758NE20 or NFD148574DE11. Need to remove the NE20 and DE11 respectively.

View 6 Replies
View Related
Oct 27, 2009

During work this thing stuck me that i have a column A1 an amount like 12345663..now what i want in column B1 the same amount but except first 3 digits..for example instead of 12345663 i want only 45663 in column b..

View 4 Replies
View Related
Dec 3, 2009

i have attachmed excel file. in that file total 8 colums among them i kept 8th colum empty .

but i want name racks in that file. i knw..it will confuses u. but once u see my attachemts u will realize.

eg: if G2 =XA06-G-001 I2 will be XA only..(XA is rack Name)

if G2 =XB06-G-001 I2 will be XB only

if G2 =XC06-G-001 I2 will be XC only

if G2 =XD06-G-001 I2 will be XD only

i mean to say..i want only first two alpabatics of G2 in I2..

View 2 Replies
View Related
Nov 21, 2007

I am wanting to replace part of a cell ( the first two digits) with nothing - blank

eg cell contains 441298871657 and i want it to read 1298871657

I am looking for a function to do this, i have tried using the finf and replace function but if 44 appears somewhere else in the value this will also be replaced.

View 12 Replies
View Related
Mar 9, 2012

I'm struggling to find a good formula or code produce the below. I have a column that contains a text string followed by spaces then a number. I need to sparate these into two separate cells.

OldNew NewD-EUROBANK PROPERTIES REAL E 4D-EUROBANK PROPERTIES REAL E4D-ACTIVE INVESTMENT SA 12-SYNT 567D-ACTIVE INVESTMENT SA 12-SYNT567RSK HYDRO-NWKR 25(NORWAY REG 1RSK HYDRO-NWKR 25(NORWAY REG1ADRILL LTD 1ADRILL LTD 1OFFSHORE LIMITED 1OFFSHORE LIMITED 1TRATA PLC 2TRATA PLC 2

View 2 Replies
View Related
Jun 1, 2012

as you can see in the picture;

it is possible to separate numbers from Column A into each column of B and C?

View 9 Replies
View Related
May 23, 2008

in A1 I have a drop down list with unit numbers and descriptions

example - 123456 - 30 to 150 lots

I want to see if when I pick from the drop down list the right unit and description that the cell will only show the first 5 digits.

example I pick 123456 - 30 to 150 lots in cell A1 would say 123456

View 9 Replies
View Related
Jul 29, 2008

I am triying to write a formula adding two digit in the same cell and return the sum as a single digit as shown below. but not sure how to go about doing it. would appreciate any kind souls to help out.

C5 D5 E5 F5H5I5J5K529121958 D8 F8 H8 J8 2 3 1 4Let's say if 2+9 = 11, than I wan to break 11 into 1+1 = 2, how can I get it done?examplecell D8 is the sum of cell C5 and D5 which is 2+9 = 11. than 11 is further break down into 1+1 = 2cell F8 is the sum of cell E5 and F5 which is 1+2 = 3.Likewise for cell H8 is the sum of cell H5 and I5 which is 1+9 = 10. than 10 is further break down into 1+ 0 =1cell J8 is the sum of cell J5 and K5 which is 5+8 = 13, than 13 is futher break down into 1+ 3 = 4.

View 9 Replies
View Related
Feb 27, 2009

I format the cells with ########## but it does not add the neccesary 0's if its not 10 digits, am I doing something wrong?

Some of the cells are 4 digits, some 7 some 9 etc i need all 10 with trailing 0's

View 9 Replies
View Related
Jun 23, 2009

I need a function that can find a single digit in the cell containing the shifts worked. As an example, the cell may contain 1, 2, 3, 12, 13 or 23. I have tried using the FIND function, but so far I have not been able to make it work. I will continue to research the FIND function, but a function that would search a cell "containing" a specific digit would be ideal. If there is such a function out there.

View 9 Replies
View Related
Mar 30, 2013

Our software extract for our data base sucks and values do not align properly if all entries do not have tge exact same number of fields with data in them.

I have always done the alignment in excel manually. I can find where the data starts to misalign and would like to do this automatically.

There is an id number for each entry (row) that is 6 digits long. How can I search a row for a 6 digit number and return the address of that cell? i can use this address as a starting point and use the offset function to do so alignment from there.

View 6 Replies
View Related
Jun 1, 2012

vb code that will break out each 6 digit media used in their own rows such as in the illustration below.

Sample data before macro

Master Backup

GTI - Hostname

[Code]....

View 2 Replies
View Related
Jan 2, 2012

I have a sheet with column A. This column A contains numbers from 1 number to 6 numbers.

So, one cell can have a '1' or a '999999' or any combination between them. Now this is the problem.

I want a formula that will fill those cells that don't contain a number up to 6 digits. So if a cell contains '254' I will like this formula to fill the cell like this: 000254.

This should happen with any number that don't have a number with six digits.

78 should be 000078, 78652 should be 078652 and so on.

View 6 Replies
View Related
Jul 12, 2014

I can't seem to find a way to find the max of only the last four digits of a cell, matching the first 8. As an example:

I have thousands of cells in a column like this, (and I can't add any columns to the sheet), and I have a cell with the first three digits and the second three digits. So, out of all these numbers, I want the MAX of ONLY the numbers with the first 8 digits of "800-123-". Also, the decimal on the end is how many times the number was called, and any the decimal and any number after it is to be ignored. The answer would be "800-123-0024", or "0024", I just need a faster way to find it without searching for it.

888-555-0099.2

800-123-0022.3

555-333-0474

[Code]....

View 9 Replies
View Related
Nov 11, 2008

I have a long list of 4 digit numbers:

e.g.

0234

2434

6566

4566

6785

But I only want the first 2 digits (I need the last two digits deleted). I don't want to just divide by 1000 as this will leve me with a decimal. The numbers are in text format as some of them begin with a 0. So it would be:

02

24

65

45

67

View 5 Replies
View Related
Mar 6, 2009

I would like to take the first three digits of column A and do a lookup of column B that would return the corresponding number from column C. For example, if I entered the formula for 103PH, the lookup would find the 103 in column B and return "2775.00" from column C...........

View 4 Replies
View Related
Dec 4, 2012

I have a number 53.30242 in a cell a1. How can I just make it 53.302? I don't want to round it to 3 decimal place, just keep the first 3 digits.

View 6 Replies
View Related
May 2, 2013

I have a column with social security numbers, i.e. 555-33-2222 and I need to change to show only the last four digits, i.e. xxx-xx-2222. Can this be done in excel?

View 4 Replies
View Related
Apr 13, 2007

I have 2 columns, A and B. The data looks like this:

0040005A2002868000PMTo 164.40

003000005000037000PMTo 104.40

001000002002090000PMTn 188.35

002000002000015000PMTn 104.35

001000002000298000PMTn 92.80

001000004001042000PMTo 78.00

001000004001050000PMTo 78.00

003000001002100000PMTo 97.10

001000004002115000PMTn 92.75

I want to have column J with values from column A grouped by the first 9 digits and column J with the totals for these groups. It would look something like this:

002000002 250

002000004 300

003000027 100

003000050 70

004000002 90

etc

View 9 Replies
View Related
Aug 24, 2008

I have a column of numbers, all with varying numbers of digits. I want to make them all have only 4 DIGITS in total (regardless of where the decimal is located... so there could be 4,3, 2, 1,or, 0 decimal places). I just want to make everything the same number of digits.

View 9 Replies
View Related
Sep 7, 2008

In this worksheet , in Columns B4:F2615, there are rows of digits that range from 1-36, I have a need to find the 10 digits that were drawn togather the most. I have no idea how to do this in Excel or can it be done? ....

View 9 Replies
View Related
Nov 4, 2008

I have a pair of digits in col B, that I would like to match with the digits in in col D, and display those matches in col E. If possible I would delete the duplicates in col E, and show results in col F.

View 9 Replies
View Related
Jun 24, 2009

I have a formula now that is =right(C2,5)+0 that is working well. However the data has grown and sometimes there is also 6 digits now instead of 5. So I need it to pick up either one 5 or 6. When I change the formula to 6 it works but picks up a / which happens to be before the 5 digit # sequence when there is only 5 digits. It works great for the 6. Is there another way around this so I only get the numbre digits if there are 5 or 6 and not the /. Maybe an if statement. I've tried several ways but none work right. The only other thing I can think of is to get it as above with the =right(C2,6)+0 and then afterwards to a find and replace and remove the / from the data. I was just tryign not to add an extra step to the process. Any ideas please?

Example of the data in coloumn C2 is:

15/2000/4567/NA/NA/97305or with 6 digits at the end15/2000/4567/NA/NA/973052there is always just 5 or 6 digits at the end that I need.

View 9 Replies
View Related
Jul 8, 2003

I tried every filter function I know of, to no avail, and am yielding my stupidity to the forum. I have this series of numbers as an example:

456912

789547

785171

658712

968712

369874

258741

127812

All I want to filter is all values ending with 12.

View 9 Replies
View Related
Jul 31, 2007

how can i do this in excel 2002?

for example.....

mod(95000000922019182020281000,97)=24

but in excel im getting the value as 0

also when i type the 24 digits its show like ..... # NUM!

if i put the function like =A1-FLOOR(A1,B1) its working for 13 digits but not for 24 digits.

View 5 Replies
View Related
Dec 14, 2007

I've recorded a macro to replace the Australian telephone number area codes (at the beginning of each phone number) with international dialling codes. I also need to replace the first two digits ONLY of mobile ( cell) phone numbers which, in Australia, all begin with "04" (see last part of macro code below - column L:L).

With the code the way I've recorded it, if it finds "04" in another part of the number (e.g. 0411 104 111), the second occurrence of the "04" will automatically be replaced with "614" as well which I don't want. So I need some code to add so that the macro only searches and replaces those first two digits in that column. I hope I'm making sense?!

Sub ConvPhNo()

'

' ConvPhNo Macro

' Macro recorded 12/12/2007 by xxxxxxxxx

'

'

Columns("C:C").Select

Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _ ...........

View 9 Replies
View Related
Apr 26, 2007

I did post a problem where I have a number like 123456 and I need to have Excel change it so that it puts a comma before the last two digits .. like so: 1234,56

I got a reply where I got the solution to use 0","00 and this works in Excel (using the custom format)

The only problem is that although the number changes in the cell to 1234,56 it doesn΄t do so in the FX window and thus when I use the number to multiply it is actually 123456 instead of 1234,56 like it want it to be.

View 13 Replies
View Related