Replace Some Words In Cell With Those From Lookup Table
May 29, 2009
We have a list of typed information (see attached) in which we would like to remove some words and simply replace with a space "". In addition to this: Unfortunately, folks tend to mistype or misspell words, add extra digits, etc, and for those mistakes we would like to replace them with corrections. Therefore, for this problem, we have a Replacement Table and the Subject Data, an example of which is shown in the attachment. I have posted a slightly similar problem on Ozgrid recently:
Replace Words In Cell Matching Those In A List
and in that link, the following UDF was proposed:
Function ReplaceIfInList(rList As Range, rCell As Range, _
strReplacement As String) As String
Dim rLoop As Range
If rCell = "" Then Exit Function
For Each rLoop In rList
If ReplaceIfInList = vbNullString Then
ReplaceIfInList = Replace(rCell, rLoop, strReplacement)
Else
ReplaceIfInList = Replace(ReplaceIfInList, rLoop, strReplacement)
End If
Next rLoop
End Function
This worked great, but at the time i posted this, I did not have the foresight to anticipate the flexibility in what the replacement text would be (the above code always replaces with a ""). THIS new Post is asking the following:
Is there a way to define (or modify the above) User Defined Formula that will analyze the words typed in a cell, which are typically separated by spaces (or on accident, multiple spaces), and essentially use the Replacement Table as a reference for what word to replace with?
View 5 Replies
ADVERTISEMENT
Dec 7, 2011
I am using excel 2007. I have a worksheet with a list of words I wish to step through this list replacing the selected words in another workbook with nothing (ie deleting them).
I have the following code
Code:
Sub replacewords()
Dim MyWord As String
Dim wbLibrary As Workbook
Dim wbWorking As Workbook
Dim myExcelColumns As Integer
Dim myExcelRows As Integer
Dim MyRow As Integer
Dim MyRange As Range
[code].....
It does not seem to evaluate "myRange";
View 2 Replies
View Related
Apr 27, 2009
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
View 3 Replies
View Related
Sep 20, 2006
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies
View Related
May 21, 2009
Im hoping there is an Excel Formula (not VBA Code) solution to this problem.
I understand how to use the Substitute command in Excel, but I would like to be able to provide a list of words and have the Substitute command use that list to replace every occurrence within a given cell with a blank ("").
The twist to this is, that the List of Words will be Dynamic, and thus the formula will need to account for that.
NOTE: the formula should NOT replace parts of words, e.g., if the List Word is "can" and the cell to be evaluated contains "candle" the formula should NOT replace the "can" in "candle" with "". Only whole word matches should be replaced.
View 9 Replies
View Related
Mar 25, 2014
How do you replace terms, such that if you replace 'alphabet' with:
alpha -> calcu
bet -> lator,
you get 'calculator'?
The one I'm asking is similar to: Replace("alphabet", "bet", "lator") = alphalator, though 'alpha' will get replaced as well.
View 2 Replies
View Related
Jan 5, 2010
I'm trying to write some code that will make excel examine a particular range, determine if there is text in each cell, and if there is replace it with different text.
View 6 Replies
View Related
Oct 31, 2012
Trying to do:
Where ever "New Orleans Saints" shows up in my sheet. I want to replace it with "NO"
Where ever "Washington Redskins" shows up in my sheet. I want to replace it with "WAS"
.
.
.
This continues on for 32 different teams.
View 4 Replies
View Related
Dec 23, 2013
I'm trying to get excel to check a cell against a table of numbers and if it finds any of them to replace it with the correct number.
Basically if the cell contains any of the numbers in the "Do Not Use" column then it should display the corresponding entry from the "Replace With" column?
see attached spreadsheet. Cells in orange are where I want the formula to go...
Test2.xls
View 7 Replies
View Related
Jun 21, 2013
how to accomplish more work quickly is proving difficult. I am making a word cloud of survey responses for my boss and he doesn't like to see similar words in the cloud (like Ease and Easy or even Fast and Quick). I have several hundred of these responses that are open ended answers that say one or many of these terms. Any way I can separate all the words and have them in one neat column? If I can get to that point, replacing those words will be simple enough.
View 2 Replies
View Related
Dec 30, 2008
I'm having a slight problem with lookup. I have attached the workbook. On the worksheet "Time List", it shows each round what time each team plays in order of time. On worksheet "Ind. Times" it is supposed to list the game times in order of team rather than times.
I'm using lookup, but the values in "Ind Times" are incorrect. If you look at the very first value... It says Monday Night Roast have a BYE Round 1, but looking at "Time List" you see they actually play at 8.00pm. On a side note, if I change the values of the team names to numbers - it works no problem at all.
View 3 Replies
View Related
Apr 29, 2014
I have two sources of information that I need to look up on, but one of the sources contains other words within the same cell?
For Example I have a source of data which contains all our client names, and I need to search for the client name in a second source of data to bring through the value from another cell, however the table array that I would do the look up on contains both the client name and project name together in one cell - therefore I am unable to do a normal Vlookup - also the client name is not always the same number of letters.
See an example:
Source data 1:
Client name:
XYZ Ltd
Source data 2:
in cell A1 XYZ Ltd/project ABC
in cell A2 shows the value e.g.: £5000
Would there be anyway to do a look up on XYZ Ltd to bring through the value in A2 on source data 1?
View 5 Replies
View Related
Jul 31, 2008
I have an issue with VLOOKUP. By runnig VLOOup we can get the data that there is present in a specified range. But how about to get the data DISPLAYED which is not there in the specified range. I tried combining IF and VLOOKUP functions. I am on a simple project now and I would be happy if I could get the answer immeidately
View 2 Replies
View Related
Oct 9, 2008
I want to make a lookup on a string of words from one of my worksheets and I also whant to make a hyper link so that when I click on the lookup command I should pop to the other worksheet were my database is.
View 2 Replies
View Related
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Jun 12, 2006
I am trying to use Excel to do my manpower allocation and planning. So I have
a column of employees and a row of months. I then put in the project name
that each is working on in a given month.
then at the bottom, I just sum the number of people working on a particular
project in a month and do a simple calculation between the number of
available people and the number who are allocated. This works beautifully IF
each person only works on one project in a month. As we know, this is an
unreleastic assumption!
What I would like is for me to put in multiple project names in a cell and
for the 2nd table to be smart - i.e. if I put in Project A & B for John, then
it should count John as 0.5 for each A & B not 1 for each A&B. Similarly if
John is working on 3 projects, then it should count only 0.33 for each
project John is working on.
View 11 Replies
View Related
Oct 12, 2009
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
View 4 Replies
View Related
Oct 27, 2013
What I a trying to do is I have a sheet with 3 pages and am using excel auto complete to change data in my cells to save typing out every time. for example if I type 22.. in sheet 1 cell a1 and tab away, it replaces it with an address and post code stored in auto complete. is it possible to store my info in another sheet, if it matches the 22.. in say sheet 3 replace it with the text in the next cell to the right
View 5 Replies
View Related
Apr 22, 2009
I have a Workbook(wb1) that pulls information based off of IF(and( and vlookups from multiple other workbooks(wb2 thru wb12). Is there a way the member typing in wb2 - wb12 able to change the Tab name one each sheet? Since the formulas i use use the tab name on the sheets for referencing. I'm hoping to do the example with out any user input.
Example ....
View 9 Replies
View Related
Oct 16, 2007
I have multiple IF functions in a formula and found out that the maximum allowed is 7 and should use Lookup instead. The formula is to calculate the Present Value of an amount with the corresponding interest rates and number of days left.
View 3 Replies
View Related
Jun 9, 2008
looking up a value in a table.
So I have this value on one worksheet: Example 1
Now, I have a table on the second worksheet with a bunch of id's in list of 1-100. Now, each product has one of those id's and also has a product name.
So, right now, I am trying to figure out what the product name would be if my id specified is a valid id in the table on worksheet 2. Now, if the specified id isn't a valid id in the table (for example 105) I need the cell the say, "No Product".
Here is my if function: =IF(what do I put here,VLOOKUP($A$1,Table1,2), "Product Not Found")
So what do I put for the logical test to find out if the id is a valid id in the table that i'm looking up the id's.
The bottom line: If you get an error like #Value! or #N/A, or any of those types of errors, how can you rename the cell so say something else instead of that.
View 7 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Jun 5, 2014
I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?
I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.
i.e.
A B C D
territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555
and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.
View 3 Replies
View Related
Mar 18, 2009
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
View 2 Replies
View Related
May 1, 2007
I need to place a lookup table in a work book and I'm not sure how to do it.Below is what I specifically need in my workbook.
c. The workbook will need a lookup table that will lookup the tuition, clothing
and entertainment figures depending on the selection of college, and will
ensure that only the colleges on the list are selectable. That is, the
worksheet will not allow the user to enter another college not in the list.
The lookup list must be on a worksheet by itself at the end of the workbook.
View 13 Replies
View Related
Aug 4, 2009
I am attempting to do a search on a 2 dimensional table, based on Product Code/Month. Is there a function, addin or a macro that will allow me to do this? All I can come up with is a messy combination of VLOOKUP/HLOOKUP/COLUMN.
View 2 Replies
View Related
Aug 13, 2008
I'm trying to convert our commonly used pivot tables into formulas based static tables. I've made great use of DSUM for most of them, put I'm not sure if this is an option for this one.
I've attached a sample of the spreadsheet I'm working with. I need it to search for the specified Area, Account and Month, and then give me a list of companies and their subcategories, and add together the totals for them. My problem is that I don't know what companies will be used each time. I can build a list of commonly used ones, but that won't help if one month we use an oddball one. Pivot tables aren't an option because size is an issue.
View 3 Replies
View Related
May 18, 2009
I have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information.
I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.
View 9 Replies
View Related
Feb 11, 2005
I'd like to use VBA to create a super substitute function. For my needs, nesting is insufficient because my substitution list is at 20 and growing. To make matters worse, the function needs to be used in several places.
What I'd like to do is have a named table with two columns for the function to use as a look-up for potential substitutions. The first column would contain the original text and the second would contain the replacement text. This way, whenever new items come up, all I have to do is add them to the list. The syntax of the function would be along the lines of SUPERSUB(TextString, table), where TextString contains the text that could be modified.
View 9 Replies
View Related
Nov 23, 2011
I get a file every day that contains online orders for a client. They have made the state and country fields on the order form free text, so sometimes the State field has CA, sometimes California, sometimes even Cal or Cali. Oregon may be represented as OR, Oregon, or Ore (and one time even Orygon.)
The country field may have US, USA, United States and sometimes United States of America.
Well, they need this data imported to their system with the states fully spelled out: Oregon, California. And the country spelled out as well: United States, Canada.
Right now I have a lookup tables worksheet that I just have a macro run a vlookup on. But I'd like to know if there is a way I could just put it all in the code, without the external tables.
i.e. some code that, in English, would basically say, "If the value of any cell in column K is among these values, replace it with those values, otherwise just leave it as it is". Could I do that while containing the two sets of values right in the code rather than on a worksheet?
I really just want to replace the standard abbreviated state/country codes with the full text, and if the value does contain Cali or Ore, let them manually make the change. Hopefully that will finally nudge them to just make the online order form utilize a drop-down list of states and countries, rather than free text.
View 1 Replies
View Related