I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
I am trying to count the number of specific words contained in a specific cell. If my data were static, and the list of keywords was short, there are many solutions such as using multiple instances of the length function as proposed by @shg here.
Suppose I have the following sheet: (column C is what I am trying to achieve)
A B C 1
That is, C2 will tell that B2 contained 3 of the keywords (1X beautiful, 2X happy) and so on.
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
Formula that could find the result and return the greatest text value?
I have over a 1000 records broken down by Leaders of which each is roughly 50 records. in those records, answers are either : 4 hours, 8 hours, 24 hours or 48 hours. I need to have a formula return of the 4 choices, which had the greatest value.
I want a formula or function that will return a count value if a cell contains a string rather than returning a value if the cell equals a value. For example, =COUNTIF(A$1:A$19703,F1) returns a value to cells in column G if the value in column F equals any exactly matching value in A. I need a formula that works like the filter option "contains" so that if any cells in col A merely contain a string rather than exactly match or equal a string from column F I can get a count value returned.
I have the following formula that returns the most frequently occuring string in a range. what I would like to do is return the 2nd, 3rd 4th and 5th most common occourance as well. I am not sure if I can do this by adjusting this formula or whether that would be a completely diffent formula or worst case senario it is not possible at all.
Cell H1 has a variable string of references for eg "FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
I manage to do a proper search and the return value is correct, but the problem is when I don't get the correct value excel gives me an error, what I can add to make the result just to give me MsgBox "Not found"?
Set Ran = Worksheets(2).Range("A:A").Find(CompName, lookat:=xlPart) If Not Ran Is Nothing Then MatchRow = Ran.Row MatchCol = Ran.Column End If
I have two different functions, first is importing website to excel and the a second is testing string according to pattern. Each one of them is working ok. I'm trying to find a word " finance" in URL and put it into cell "A1".
Sub ParseWebsite() Application.DisplayAlerts = False On Error Resume Next For i = 1 To 10 SiteURL = "URL;http://www.cnn.com" With ActiveSheet.QueryTables.Add(Connection:=SiteURL, Destination:=Range("A" & i)) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False................
Cells. Find(What:=searchterm, After:=Cells(1, 1), MatchCase:=False).Activate 'find pn that is stored in searchterm
causes a runtime error 91 when there are no results. "Object variable or with block variable not set"
Ideally, when there are no results, I would like to enter a loop, where I could provide a drop down list of fuzzy matches, but I am not sure excel can do fuzzy matches. I'd settle for just highlighting the row and moving on... any help?
ActiveSheet.Cells.Find(What:="AES", After:=Cells(1, 1), MatchCase:=False).Activate 'find the aes part number column Dim aescolumn 'create variable for index value of aes column aescolumn = ActiveCell.Column 'set variable to index number
I am trying to search through column "I" for numbers that are decimals, when the first decimal is found, use the row number that the first decimal appears in the string that populates the bookmarks stored in a word template I have created. The code I have so far checks for the decimal and then populates the bookmarks, however it requires me to input the ranges manually, meaning I can set them as the first row in my tabel. But if the decimal appears in the second row and not the first I'm bookmarking the wrong data. Here is the code I have so far;
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.: - Cell A2 contains "randomtext,randomtext,APPLE,randomntext" - I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT. - I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String StringReturn = Str1 & Str2 & Str3 End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Cell A1 has 123XVF1234 Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones Column B : payroll number ie 123456 Column C : shift times ie 1245-2124 Column D : job title ie floor Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?