Replacing Misspelled Sub-string In Varying Full String
Aug 27, 2009
I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:
Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2
I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.
Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...
Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".
Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.
Here is the format of the report at this point. Names have been changed to protect the innocent.
Agent: 2366 Bacon, Kevin
Date Start End
[Code].....
So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.
I know what I want to do...but not shure on the code cause I am not great at VBA.
Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.
In the attached excel, I will be putting a string (Which will be a result of another formula) and there will be a table in column A & B(Length of the table will vary).
What I need is a formula to search entire column B and get the file names which is having that string and also the corresponding path
Table E6 to F9 contains the desired result (The result wont be more than 3 at any point of time
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):
VB:
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?
Example;
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
Example;
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?
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'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!
I am trying to lookup if a string like a name exists in another string which has a buch of values for example to see if andy exists in a string which has data like andy;sandy or sandy exists in andy;sandy
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:
[Code].....
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.
I have lengthy title names such as "Giant_SOW_20141203 clean US-OPP134163" which i need to extract the ID number which is US-OPP134163. The ID number is the same every time starting with 'US-OPP' and then 6 numeral digits. I am looking for a formula that will extract this ID number from titles. see the file attached for examples of title names and what my end project should look like in column B.
How to extract out the (M) from a string and convert (M) in that string to another value? Example: I want to extract out the (M) from a string and convert the (M) to "Medium". The data is: Burger (M) with french fries. How to exract (M) from the data and convert the value (M) to "Medium"?
How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example: .....
I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
Those lists will be called up based on 7 values in column “D”: “G” “152” “J” “X” “D/E” “V” “R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x 504 that show up 3x 24 that show up 12x
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1 I have a module called Test2 and within this, a macro name called test2 etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3() Test1.Test1 End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3() Test2.Test2 End Sub etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3() MacroToCall = Sheets("Sheet1").Range("A1").Value MacroToCall.MacroToCall End Sub
I am trying to write in VB some code that will enter a number in a column if a cell has some data in it and one character is an @. see attached spreasheet.
With VBA, I'm attempting to locate a DB, path will remain constant, but the DB name will change to whatever date it was last updated ("2012-02 Uptime"). I want to return that name, but only if the name does not contain a string ("Updating," for when it's being manually updated -- I'll just msgbox/exit if this is the case).
Application.FileSearch was my natural instinct, but Excel 2007 has that deprecated, and I can't figure out how to do this and still check for the word "Updating" in the filename.
say I have two files named "Model Template" and "A_Model" and I am trying to allocate the file names to string variables. The file names represent 'families' of files with varying names that can be distinguished by the presence of the strings "Template" and "_". Why would the Instr method as used below not work?
Sub WorkbookNamesStorei() Dim Workbooki As Workbook Dim NameWorkbooki As String Dim SensisBook As Workbook Dim NewModelBook As Workbook Dim a As String, b As String a = "_" b = "Template" For Each Workbooki In Workbooks NameWorkbooki = Workbooki.Name If InStr(1, a, NameWorkbooki) > 0 Then....................
I have a string that follows this format: SAMSUNG CT797F-1
From this string I need to extract 797F only. Further complicating the problem is that its not always SAMSUNG it could be ABB CT797F-1 or KOMSATISU CT797F-1
I have implemented a function that can extract all the numbers, but can't think of how to extract 797F from the various different strings.
Given 5 rows in column A, I have strings that has the word 'Qty' and some none. For any string that has the word 'Qty', I want the numeric value before it. For string that doesn't have the word 'Qty', I want it to be 1. My question is, how should I added the numeric string without a helper column?
Column A Desired Qty to Take
some string name and ended with (4 Qty) 4
some string name without the word Qty 1
[code]....
In the above example, I would like to have an answer in say cell B1 to be 15
