I'm looking for a formula that can determine whether a given sub-string appears in a string. For example if cell A1 contains the string "bathing", and cell A2 contains the sub-string "bat", the formula in A3 would return "TRUE".
However, if the sub-string in A2 is changed to "cat", the result in A3 would be "FALSE". The formula would need to work with strings of different and unpredictable lengths.
I have a 2007 Excel spreadsheet (saved as .XLS) with worker names in column A and error types in column B. Column B can have multiple entries (which are sometimes duplicative of each other) separated by a hard return.
What I need to do is run tallies to determine the number of errors by type for each person, counting the value every time it appears, even if it is more than once in a particular cell. The ultimate goal is to generate a formula to track the number of occurrences for all error types types for the person in Column A (i.e. one formula each to track ABC's Procedural errors, ABC's Technical errors, ABC's Admin errors, DEF's Procedural, etc) though ideally I just need a formula to calculate any one of those and I can edit it to get the rest. Here's a sample screenshot:
The COUNTIFS formula is where I started but that only seems to count cells with the value as opposed to occurrences of the value. I did find this formula in my searches but it doesn't seem to work:
when I open my sheet on another computer, all cell value which contain formula show no value. just blank. formula is link to same workbook on different tab. when I save as again on my desktop, all value appears. excel 2007 is using.
I am trying to find specific text throughout an excel document (2007) and replace it with the same text but in italics. I tried using the options/format function and selecting italics for the "replace" text but it replaces the entire cell text in italics instead.
I export data into Excel format from a corporate reporting tool. 1 column includes a product description in text format, however, due to the many different products I need to count the number of cells based on a single word in the product description.
The report contains various information about vehicles. The product description exports to a single column and may include "Ford Fusion", "Ford Focus", "Chevrolet Malibu", "Chevrolet Impala", etc. I only need to count how many cells contain information about Fords and Chevrolets. The model detail is not needed.
I'm able to count if I enter the complete and exact make & model description, but want to avoid this due to the large quantity of products.
I'm using Excel 2007, on Windows 7, 64 Bit Enterprise
We've got an end user who opens a .dat file which is delimited with semi-colons, replaces some text in a couple of columns then saves as a text tab delimited file. She swears that before today, the saved file would retain the semi-colons. Now she says it's not. she generated a new .dat file for me and recreated the process, with the resultant semicolon-free file. I asked her to generate another one and send it to me. I opened it first in notepad, and saw the semi-colons.
Opened it in excel (I'm using 2010 on win 7 professional), which saw it as a delimited file and asked me to specify the delimiter. It shows up fine, all the columns are righteous. I made no edits and saved as a text tab delimited, and that saved file had no semi-colons in it when I opened it. This seems normal to me; there's no semi-colons in the excel file, so why would there be in the text tab delimited file. I asked her coworker to generate another .dat from his pc and let me know what he sees, but he hasn't reported back yet.
I am struggling with doing a sort, of a group of alpha characters within a cell (Excel 2007). My data was exported from Oracle 8 as a varchar2 and consists of letters.
Example of what I have BAC GHL DFE MEG
Example of what I want ABC GHL DEF EGM
All I could find were these complex looking solutions involving cursors, plsql, etc. I'm hoping there's a simple solution, maybe within Excel. I've tried different formatting, made sure no other characters were sharing the cells, and tried typing fresh data into a cell (instead of the exported data).
I am running EXCEL 2007 on XP. What is the EXCEL cell formula to put in a cell (for example"E5") that will reference the "last" occurrence of a specific text string in column A (For example Chard ) but will show its corresponding column B content (for example its PH number 3.45) regardless of the number of data entries that will be inputted in the future for both column A and B.
A B C D E 1WINEPH 2Chard3.24 3Merlot3.36 4Cab 3.44 5Merlot3.38 xxx 6Chard3.26 7Chard3.45 8Cab 3.41 9 Merlot3.33 10
I just wrote two macros that each produce separate text files (call them 'A' and 'B'). I want to open 'B' with Excel VBA, copy all of its contents, and paste that content into 'A' right after a specific location in 'A' (where I have 10 consecutive asterisks, i.e., **********).
Most topics relating to Excel VBA and .txt files have to do with either importing / exporting into Excel (not what I want), or with associating .txt files to Excel (also not what I want).
Ii have a UserForm with a ComboBox in it. the selections in the box are
0 5 10 15 20 25 30
I have 7 shapes in Worksheets("Interface") and renamed each shape caution1 through caution7
What i need is when i click on continue in my userform each shape will change according to the selection.
if 5 is selected all caution shapes will read, Caution flag is out 5 minute break if 10 is selected all caution shapes will read, Caution flag is out 10 minute break if 15 is selected all caution shapes will read, Caution flag is out 15 minute break and so on
My set up win xp pro office 2007. Object is to save the text from a given cell to drive C naming the file with the value in another cell. E.g.
Contents of cell ("AA5") is a string and the content of ("D5") is a number
I have tried altering the code posted which i found on the forum, it works fine for the purpose as it was indented e.g. save the workbook and in my case saves it as the value in cell D5.
I guess I need to use some form of object to pass to the save as method but my knowledge of this is somewhat limited in addition as the is a possibility of either the cell being empty and the file name existing on the C drive, I need to disable the windows warnings and just go ahead and save it over writing what is on the C drive is of no importance as it will be the same data anyway, I can wrap the save in a "if Len ( AA5)= 0" to stop it trying to save if the cell if empty it the save the text string i am having problems with:
Option Explicit Sub SaveAsExample() Dim FName As String Dim FPath As String FPath = "C:" FName = Sheets("Sheet1").Range("A1").Text ThisWorkbook.SaveAs Filename:=FPath & "" & FName End Sub
I'm creating my task file in excel 2007. In this file there is column for task description, Intimation date, start date, Set completion date, completion date and remarks. I want in intimation date cell, cell contain the date which is 5 days before the start date and the cell/text(date) blink until the start date come. if the task completed in the defined completion date, "complete in time" is written automatically in remarks column, and if completion date is after the set completion date for the task "delay in completion" written automatically in remarks column.
Sr. No. Intimation date Task Description Start Date Set Completion Date Completion Date Remarks
I have a question about conversion of a text format into date format.
2 computers, the first windows vista, the second Windows 8, both Excel 2007.
The purpose is to calculate the difference between 2 dates with hour:minutes:seconds, like for example: 18/12/2012 19:20:00 and 18/12/2012 19:23:03.
So in this example the result should be 00:03:03.
Both dates are formatted with 'personalized' : dd/mm/yyyy hh/mm.
In the first computer everything works like it should, but in the second it is apparently impossible to transform the first of those date h:m:s which is originally in a text format into a date format. I tried all the methods which i found to transform a text date format into a date , like multiplication with 1, Datevalue and others, but nothing seems to work, the result is always "#Value".
The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)
However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.
Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)
Part #|Inventory ID 1743|213,221 1864|10,40 1948|1170,1180 5265|100,104,107,10004
Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.
When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.
Here's what I'm left with: Part # Inventory ID 1743 213,221 1864 10,40 1948 11,701,180 5265 10,010,410,710,004
The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.
When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.
I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.
How to best retain the format of my Inventory IDs.
I have an Excel 2007 spreadsheet with one text based column containing some large text data. I want to be able to have the text in this cell reduced to show certain fixed number of lines(maybe 2) and where the text cuts off, display an ellipsis which indicates that there is more text. The user should be able to see the full text when that particular cell is double clicked.
This will allow my rows to be of uniform size still allowing the user to look at the full text when double clicked and an ellipsis as an indication to the user that there is more text than what is displayed.
A1IDS09C1_PEND_COB2COPY YES SHRLEVEL CHANGE SORTKEYS SORTDEVT SYSDA3BMCSTATS YES UPDATEDB2STATS YES UNLDDN RU0014CONDEXEC YES UNLOAD RELOAD REDEFINE NO DELETEFILES YES SORTNUM 125DDTYPE WORK ACTIVE YES DDTYPE SORTWORK ACTIVE YES6DDTYPE LOCPFCPY ACTIVE YES7DSNPAT
In other words, I need to copy the text in blue so that it is above the text in black and the text in red so that it is below. Then I need the macro to repeat this procedure for each cell that it finds containing text in black. I have already created and run a macro to insert blank lines between each black text entry.
My drop downs are simple yes/no/NA witht a down arrow apprearing to the right. Works fine for all users except one troublemaker who for some reason sees the text and arrow as a mirrored, upside down image. It's not rotated to be upside down, it's mirrored so if you turned your monitor upside down you'd see the letters in the words backwards as if looking in a mirror. We're both on Excel 2007.
I am trying to extract values from a text string and add them up in Excel 2007. So far i have been successful in extracting the value out of the text string like this - =MID(I6,AD6,3) where AD6 holds the position number in the text string to start from. So it's working OK for one row but i need to do the same thing on multiple rows where the text string can be in different columns and I'd like it to automatically pick up the non-blank cell.
each row only has one column with text in it and the value i need to extract is always after "$". this is a working spreadsheet so the text string could move from column to column over time and I'd like my formula to be able to detect which column to read from. I then need to add up all the values from each row.