Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Remove First X Digits And Last Y Digits From A Cell


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
How To Remove First X Digits And Last Y Digits From A Cell
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 Replies!   View Related
Number Formatting: The First Three Digits Will Be Separated And Then Subsequently 2 Digits
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 Replies!   View Related
Remove The First 4 Digits
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 Replies!   View Related
Remove 14 Digits From The END Of Text In A Column
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 Replies!   View Related
Must Have 3 Digits In A Cell
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 Replies!   View Related
Adding Together Two Digits In A Cell
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 Replies!   View Related
Insert ONLY Certain Digits From A Cell
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 Replies!   View Related
Adding 2 Digits In A Single Cell
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 Replies!   View Related
Finding Digits In A Mixed Cell
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 Replies!   View Related
Replace Part Of A Cell (first Two Digits)
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 Replies!   View Related
Add The Neccesary Zeros If Cell Is Not Having 10 Digits
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 Replies!   View Related
Get Only First Two Digits Of Mentioned Cell Values?
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 Replies!   View Related
Format Cell To Show Only 5 Digits
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 Replies!   View Related
A Formula To Seperate Digits In A Cell
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 Replies!   View Related
How To Give Last 6 Digits Only If A #
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 Replies!   View Related
Filter Last Two Digits
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 Replies!   View Related
Replace First X Digits
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 Replies!   View Related
Pair Of Digits In Col B
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 Replies!   View Related
Group By First 9 Digits
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 Replies!   View Related
Count 10 Digits
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 Replies!   View Related
Significant Digits
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 Replies!   View Related
Delete The Last 2 Digits
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 Replies!   View Related
Vlookup First Two Digits
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 Replies!   View Related
24 Digits Mod Function
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 Replies!   View Related
Using Substitute When There Is Text Followed By N Digits
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".

I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)

I understand how to use IF and substitute. its the 7 numbers part i am stuck on.

I could do it in access with the wildcards but excel is different.

View Replies!   View Related
Formula Returns Two Digits Value
I have a formula that looks like this:

=IF(ISERROR((C101/G119)*G104)," ",((C101/G119)*G104))

There are about 15 cells with formulas similar to this. They are calculating a value for a row and at the end all of them should add up should equal C101.

The problem is the value that formula returns has to be two digits and I know it almost never is. But it ALWAYS equils C101. However because it only shows two digits somtimes if you manulally add up the value that you see it does not its off by +/-.01.

I need to fix it so that it does equal C101 AND if you were to add up the digits (with 2 decimal places) it will also equil C101.


View Replies!   View Related
More Digits From Polynomial Regressions?
The functions of trend lines in charts are nice, but the coefficients of the output equations are all with only one-digit for the polynomial fits. The single-digit fits by the fitting equations often result in higher deviations from what we have seen in the charts.

I also tried to use the statistical functions but it seams that I cannot get any outputs of these coefficients except for the slope of LINEST, which is not sufficient for polynomials.

I also use addins such as as regressions but cannot get anything either to help for these coefficients.

May someone tell me how to make such outputs with more digits so the equations can be more precisely fitting please?

View Replies!   View Related
Varying Lengths Of Digits
My data is as follows, all in one cell:

10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21

There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))

My result is 9,423.52. Its not pulling the whole number.

What do I need to change in the formula?

View Replies!   View Related
Split Number To Digits
Can a vba macro be provided for splitting a number into digits? The number will be in Sheet 1 but splitted number will be on sheet 2. Splitting of numbers means a number entered into a cell will be splitted into different column/cells with one digit per cell.

View Replies!   View Related
Evaluate Digits Within Numbers
Example numbers:

21130 & 21065

I want to check each number if EITHER of the two conditions is true:

1. if the third digit from the right (the hundreth place) is greater than zero;

or

2. if the second digit from the right (the tens place) is >=6.

If either is true I want to add a particular number to the original number.
My example numbers meet questions 1 & 2, respectively.

View Replies!   View Related
Convert Numbers To Digits
this may be simple but I need to convent say
A1 = 09
B1 = 23

into

C1 = 0
D1 = 9
E1 = 2
F1 = 3
..etc

View Replies!   View Related
Textbox To Accept 12 Digits
below code. I need to change this code to accept 12 digits.

View Replies!   View Related
Puts A Comma Before The Last Two Digits
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 Replies!   View Related
Match 3 Hold Digits
very simple, I would like to know how the match function could be used example:

125
457
706

match 457 = 1


View Replies!   View Related
Sum Up The Digits Within A Range Of Cells
I am looking for a formula to sum up all the digit(s) within a range of cells, e.g.

View Replies!   View Related
Display First 4 Digits Of A Number
I work in a finance department and we have MANY numbers consisting of 7 digits. Is there a way to enter in the entire number, but only display the first 4 digits?

View Replies!   View Related
Converting Dates To 8 Digits
I have dates being imported (AS TEXT) to excel in various formats and want them all to appear in their cells in the same 8 digit format (MMDDYYYY) so it can be exported to another program

for example:

Single Digit Month and Day
cell A1: 3179 (convert to 03011979)
cell A2: 30179 (convert to 03011979)
cell A3: 030179 (convert to 03011979)
cell A4: 311979 (convert to 03011979)
cell A5: 3011979 (convert to 03011979)
cell A6: 03011979 (already correct)

Single Digit Month and Double Digit Day
cell A7: 52165 (convert to 05211965)
cell A8: 052165 (convert to 05211965)
cell A9: 5211965 (convert to 05211965)
cell A10: 05211965 (already correct)

Double Digit Month and Single Digit Day
cell A11: 12523 (convert to 12051923)
cell A12: 120523 (convert to 12051923)
cell A13: 1251923 (convert to 12051923)
cell A14: 12051923 (already correct)

Double Digit Month and Double Digit Day
cell A15: 112180 (convert to 11211980)
cell A16: 11211980 (already correct)

View Replies!   View Related
Run Macro After 4 Digits Entered
I have a macro that is used several times a day. Four digits are entered in cell E5, then I press Enter, then click on an icon that has a macro assigned to it.

Is there a way to trigger the macro after simply entering the four digits? Or can the macro be triggered after entering the four digits then pressing Enter?

View Replies!   View Related
Formatting Specific Digits...
is there some way to conditionally change the last digit into something else entirely? 1222 should be 122.25, 1224 should be 122.50, and 1226 should be 122.75.

View Replies!   View Related
Make Last Two Digits Invisible
Is there a way to make the last two digits in a number not show up (make them white) and also cut off the last two digits?

View Replies!   View Related
Move 1st X Digits Of Number To End
I need a formula to extract the first two numbers and move them to the back of the number remaining. For example, the original number is 235871, the result would be 587123.

My numbers always have six digits but may at times have seven.

View Replies!   View Related
Rumble 3 Digits From Pair's
How to make Rumble 3 digits from pair's

View Replies!   View Related
Sum Digits In Range Of Cells
I am trying to sum the digits in a range of cells, as per the attached example.

I modified a formula I found on this site for summing digits in a single cell, to deal with a range, giving this, entered as an array formula.

{=SUM((LEN(A5:A9)-LEN(SUBSTITUTE(A5:A9,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})}

This works for cells in a cloumn, but not for a row or a block.

How could it be modified to deal with a row or block?

View Replies!   View Related
Macro Returns Only 15 Digits
I am using Excel 2003. I am trying to importing a 20 character from a barcode scanner to cell K2 in an Excel spreadsheet. When the part is scanned, the program handling the scanner data shows the data as being 12345678901234567890. I am running a macro that retrieves that data and places it in cell K2. The number that appears in cell K2 is 12345678901234500000. The macro module code reads:

Sub GetSWData()
Dim WedgeData As Variant
Dim MyPort As String
Dim Chan As Long
Dim StringVar As String
Dim DataIn As Double
MyPort = "COM1"
Application.DisplayAlerts = False
Chan = DDEInitiate("WinWedge", MyPort)
WedgeData = DDERequest(Chan, "Field(1)")
DataIn = WedgeData(1)
Range("K2").Value = WedgeData(1)
DDETerminate Chan
End Sub

I am guessing that one of the Dim statements has the wrong word after As but nothing I have tried gets me past this point.

View Replies!   View Related
Delete First X Digits In Numbers
I have a unique 6 digit number in a column. I have 2500 rows. I need to cut the first three numbers only leaving the last 3 numbers in the column for each row.

Example:
112345
234325

I would need 112, and 234 deleted. Checked the forum and cannot get quite what I want.

View Replies!   View Related
Extract Two Digits From Number
I want to extract the fifth and sixth digits from an 8 figure number i.e. 20010891 would equal 10.

I have tried every combination of Left & Right combined with Find that I know and nothing works.

View Replies!   View Related
Reduce Numbers To X Digits
I have a column with numbers in about 500 rows. The entries are 5 numbers long and others 8. So I thought i could use one of the following: A macro code to tell a cell to delete the first 3 numbers if the entry is 8 numbers long?

OR

A macro code to tell a cell to reduce itself to 5 digits long starting from the right? Attached is a small example

View Replies!   View Related
Check If String Contains Only Digits
I'm working on a project where I need to prevent the user from entering non-digit string inputs. I've tried the using IsNumeric(MyString). but that doesn't prevent , + - signs to be entered. I also had to conditionally enable the input of a string with leading zeros like "01" so what I've ended up with is a rather messy looking' sub that handles this (don't know if forum rules allows me to show it here?). The problem is that I'm not a 100% sure that my sub fit to handle all eventualities so therefore my question is whether there is an easier, fool proof way to check my strings?

View Replies!   View Related
Extract Digits From Text
I have a string in a cell that contains a name, a 3-4 character alpanumeric code and then a 4-6 digit number, each separated by a space. An example or two:

J Bloggs SPEC 123456
J Smith AG06 9364
J & K Brown SWP 358686

I need to be able to have in a new cell the 4-6 digit number at the right of the cell. give a formula that will separate the data I need? I've tried numerous ways without success.

View Replies!   View Related
Add Space If Number Is X Digits
In my worksheet there is a column with values of either 3 or 4 numbers. If the value only has 3 numbers I would like to add a space before the first number, to ensure the proper line-up when saving the sheet as a text file. How can I do this (conditional formatting or macro??)

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved