I am trying to create a formula that sums all the cells that have numbers that begin with 12. For example, here are the numbers in cells A1 through A6 (my actual spreadsheet is much larger):
12
1200
13
1
120
112
I want to pull the 12, 1200, and 120 for a total of 1332.
in excel how do I count cells that begin with specific text. Ex: in a
column with 100 entries, I want to count the number of cells that have the
letters "app" from the word approved as the first three characters in the cell
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
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 3 Replies View RelatedDoes anybody know of a special format or a rounding function that displays or rounds a number off to a specific number of significant digits.
Lets say I would like 3 significant digits this would be the results:
0.40019 -> 0.400
6.543 -> 6.54
12.543 -> 12.5
32 -> 32.0
564.7894 -> 565
54387 -> 54400 (or 5.44E+4 to be correct, but i can live with 54400)
I am trying to apply conditional formatting to highlight the cells in D column if it starts with 91099 or 91004.
I tried to do the same using wild card, and left function but didn't get the desired output.
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
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)
I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
we query SAGE, and have account codes begining xx-xxx. We use the project ledger which attaches a letter on the end depending on the works, i.e. 00-001A, 00-001B, etc
I need to set a paramater to say any that any that begin/contain (i.e. LIKE %) 00-001 filter out, regardless of the letter.
I know the basics of [paramater] or ?, but the ? doubles as a wildcard in the like criteria. Whenever I try to put a paramter value in it shows nothing. I can get it to work for exact, >< etc but not contains or begins. Even if I play with the SQL, nothing!
I am looking for a formula to sum up all the digit(s) within a range of cells, e.g.
View 7 Replies View RelatedI 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?
I have a spreadsheet that contains private information, but I'm hoping this is a simple request. If the cell in column A does not contain text that begins (starts) with the letter "L", the row needs to be deleted. The rows that will be deleted usually start with "A2008..." "A2009..." "E2009..." etc. The row I need to keep always starts with "L", then a space, then more text.
I'm looking for VBA code that I can implement into a macro program I have already done so that once I open up a file, I can click a button and not have to do this part manually anymore.
[Code]....
I am preparing one macro and to make below macro more easier.
My Data is not constant. Everyday it get change.
In my data i am trying to delete all rows which begin with "MEANING".
I have prepared below macro but problem here is in range i am taking maximum rows i.e 65000 as i am not sure how many rows will be there in my data which begin with Meaning.
I just wanted to know what is the coding we use to go down untill data end if we are not sure about the range.
Selection.AutoFilter Field:=1, Criteria1:="=MEANING*", Operator:=xlAnd
Range("A3:A65000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
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!
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.
See attached file...I'm having trouble returning a value for the Alpha-numberics that begin with DMR, which should return 'Dishwasher'. All other values are correct, I'm sure it has to do with the character range but I'm a little stumped.
View 4 Replies View RelatedI am trying to write a piece of vba that clears the contents if the first character in the cell is not #.... if I use "#"*, I get a syntax error
Just figured my syntax needs a slight adjustment to be recognized...
I have a column that has values ranging anywhere from 1 to 999,999 and I need a way in vba to take whatever value is in the specified cell and place a decimal point at the far left. Example:
change 175526.34 to .17552634
change 376.1 to .3761
change 22987.254 to .22978254
when the code is run I need the selected number to begin with the decimal point.
See, the tricky part is that the length of the number varies and I cant just divide by X to shift so many places to the left..
I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:
12
123
234
12346
2345
[code].....
So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.
I have four different cell.
Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.
Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next rows cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies View RelatedI'm trying to use a conditional format on a range of cells so that if there is more than 1 "A" contained in any given row, the cells containing the "A"s are highlighted (e.g. red)
I've tried:
=COUNTIF(B2:K4,"A")>1
but it doesnt work & now I'm stuck.
I have a spreadsheet that contains private information, but I'm hoping this is a simple request. If the cell in column A does not contain text that begins (starts) with the letter "L", the row needs to be deleted. The rows that will be deleted usually start with "A2008..." "A2009..." "E2009..." etc. The row I need to keep always starts with "L", then a space, then more text.
I'm looking for VBA code that I can implement into a macro program I have already done so that once I open up a file, I can click a button and not have to do this part manually anymore.
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
I have a couple of spreadsheets that has several columns each containing several hundred thousand rows of codes. To quickly analyze this data, I am trying to come up with a conditional formatting formula to highlight the respective cells when specific values occur next to each other. A particular code will show up in many cells, but the code that is the respective adjacent cell is always different. I need to know when row A contains, for example, '9928559' and row B contains '36415RT'.
View 3 Replies View RelatedI have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.