Find Multiple Strings And Clear Corresponding Range?
May 17, 2008
I am currently trying to write a macro that will search a column for different strings. These strings are inside the cell (as in it is not the only value in the cell) and then copy the entire cell over to another column, then delete the two cells to the right of the originally found cell.
Heres an example of what I want to:
There are four columns, the first has a name with both the first and last (amanda white)the second just the first name (amanda), the third just the last name(white), and the forth an organization name. if the first column contains an organzation name, it has to clear out the first name and last name columns, and copy the name column to the organization column.
NAME | FIRST | LAST| ORG|
amanda white amanda white
mike jones mike Jones
pizza hut pizza hut
I have a list of organization keywords to search the first column (e.g. enterprise, variety, management, pizza). I want it to recongnise the "pizza", copy that entire cell over to the organization column and delete the first name and last name for that row.I've been trying to modify a code like this but i can't seem to make anything work.
VB:
FindWhat = "pizza"
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp)) [code]....
View 5 Replies
ADVERTISEMENT
Nov 19, 2009
I have been given a huge membership list. The field for the CITY also as the two letter state abbreviation (e.g., "Fremont, CA" instead of just "Fremont".). I want to be able to have the "CA" or "WA" or "NV" (etc) from the city field appear in a new STATE field. I successfully use the below statement to do this with the "CA" but I want a statement that will search for multiple strings (the other states).
Here is what works now: =IF(FIND(" CA",F2),"CA"). But I want to be able to add other state abbreviations to this.
View 4 Replies
View Related
Aug 18, 2008
I am having a hard time creating a code that would search the exact string in a cell. See sample scenario below.
1. I have a string "hello" to be searched.
2. I will search the whole string in each cell in a previously declared range.
3. If in a cell i found "kdhlsk hellodsiewjls", although "hello" string is there,
the code should still return 0.
4. But if in a cell, i found "kjdsfel ljsdlkue hello luoeww", then the code should return 1.
I have already created a code, but it will always return 1 in step#3.
View 9 Replies
View Related
Jan 6, 2014
Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.
Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56
Account number:987654321
Account value as of 12/31/2013:$876,543.21
View 12 Replies
View Related
Apr 22, 2013
I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below
gameballnetstick
ballgame
stick ball
tenballs
green stick
Of the three examples: it would only count cells: gameballnetstick and stickball
View 2 Replies
View Related
Mar 8, 2013
How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
View 1 Replies
View Related
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
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?
View 2 Replies
View Related
Feb 18, 2014
I'm just trying to view the results of this search, I don't need it to make changes to the cells on the bases of this search.
I'm using control + F to open the search box.
It only allows me to type in one single string, such as "Johnson". Problem is, it finds many rows that contain the name Johnson.
So I'd like to further add a number, such as 1.3338, which it can look for in any cell that's on the same ROW where it found Johnson.
It would then return, for example, Row number 81, which has a cell that contains Johnson and another cell that contains 1.3338.
View 3 Replies
View Related
May 22, 2008
I have a set of 4 columns with YES/NO statements in them.
I need to find any YES strings, of three or more, within one column, uninterupted by a YES in another colum.
EXAMPLE:
_____A ___B__C__D__E
1 1/1/2008 YES NO NO NO
2 1/2/2008 NO NO YES YES
3 1/3/2008 YES NO NO NO
4 1/4/2008 YES NO NO NO
5 1/5/2008 NO NO NO NO
6 1/6/2008 YES NO NO NO..................
View 4 Replies
View Related
Feb 4, 2012
I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.
If the exact phrase match is found, return the value True or False.
Column A
A1:Dog likes to run
A2: Cat Red-tummy-01
[Code]....
View 9 Replies
View Related
Aug 6, 2010
What I'm trying to do
A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?
I need C1 to return:
how your
Is this possible?
View 9 Replies
View Related
Nov 21, 2006
I have several cells in a column that look something like this:
Cell A1: abc 1234 def ghi
Cell A2: xxxx aa b 245 qqqqq
Cell A3: abcdefg hij kl mnopqr s
Is there an excel formula or combination of formulas I can use to identify:
(1) whether any given text string (such as those above) include numbers, and
(2) what the first number (which could contain 1-4 digits) contained in the text string is?
View 3 Replies
View Related
Jun 13, 2014
I need to loop through a worksheet to find the following product ID's: 100805, 6950000, 853000 and 20994000. Each time I find the product ID I have to execute the same code in that part of the worksheet to extract data.
I just do not know how to set up the macro to loop through each ID.
View 9 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
Apr 1, 2014
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
View 5 Replies
View Related
Sep 27, 2011
I have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?
E.g.
Before: [ +Test +Test]
After: [+Test +Test]
Code:
Columns("D:D").Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
View 4 Replies
View Related
Apr 2, 2007
In microsoft word you can clear find criteria like below.
With Selection.Find
.clearformatting
.Replacement.clearformatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
View 8 Replies
View Related
Oct 27, 2009
say that i have alist of data in "A2:A", which are a string file (text) and i want to rename each data to a certain name that defined in range "B1:B2" (String file too).
For Example: for range
C2 = S16e
C3 = 16/E
C4 = 18W
C5 = Site18 w
& The target name is to be:
B1=16E
B2=18W
For case above, i need to replace C2&C3 in to 16E, while C4&C5 to be 18W.
View 8 Replies
View Related
Jun 11, 2008
I'm building a course registration workbook and am working on the unregistration macro.
I have a seperate worksheet for each course, labelled by the course code.
There is also a page for each registered participant that shows what courses they're in.
The macro reads off the information on the participants page, and then should goto each registered course's page, look in column C for their name, find it, delete the contents of that row for Columns C to G (has their other information) and then shift all CELLS (not rows) below the now deleted cells up 1 to keep track of the order they were added.
I've tried recording my own macro and editing... but it doesn't work very well for this.
I've also tried searching and come up with nothing, but if someone can look at my code, and/or point me in the right direction, it would be greatly appreciated.
For Each ws In Worksheets
'checks only course sheets that match the courses the individual is registered in.
If ws.Name = unregisterCourse1 Or ws.Name = unregisterCourse2 Or ws.Name = unregisterCourse3 Or ws.Name = unregisterCourse4 Or ws.Name = unregisterCourse5 Then
'Should search for the variable "unRegister" in column C and select it.
Cells.Find(What:=unRegister, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
'Now needs to select the four cells to the right as well (C:G) and delete them
'Select all cells below from C:G and shift them up one.
End If
Next
View 5 Replies
View Related
Feb 14, 2010
how to reference the drang (named range) and the cell propertly as i keep getting an error.
Everything in the below code works except the parts below in blue
details:
I have in column X lots of names and in column Y (dynamic column
anges as the users chooses them as per the code) and I want to loop through all names in 'X' and if there are multiple of the same names then for the corresponding 'Y' row entry of each be checked and if they are equal (same $ value) then for one of them of cells in the Y column to be cleared.
Basically if the same name appears more than once it will most likley have the same $ value and if i do a calculation on the $'s it will be 2,3,10 times the amount it should be, hence the need for this .
As per the below code I am trying to use 2 for Each loops though I keep getting an error when trying to reference the drng range which has the values in them..
View 12 Replies
View Related
Sep 15, 2014
My tab ("dados"), has 21 columns, i need clear only column "L"
VB:
Sub CompareRemoveDupes() ' Stanley D. Grom, 04/06/2012
' [URL]http://www.ozgrid.com/forum/showthread.php?t=164160[/URL]
Dim wP As Worksheet, wN As Worksheet
[Code]....
View 1 Replies
View Related
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
View 9 Replies
View Related
Sep 16, 2009
I recently had a query about pulling data from different closed workbooks using a concatenate to easily choose the file:- ....
View 6 Replies
View Related
Aug 12, 2014
I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.
For instance:
PhraseHidden word
Please search the text for the hidden message: Blue
The hidden word, Green, may be at any point
And the word may be different lengths like Yellow
I have a table of the hidden words (Blue, Green, Yellow, Pink)
I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.
View 5 Replies
View Related
Jan 4, 2014
String Multiples for 3 different criteria in same cell -- "if", "or" "abs" --- not sure if any of these are correct
1) Criteria 1 and 2: In cell "E" I am wanting to minus "B from C", if "A" is greater than or equal to and less than "=>1000 or <15000"
2) and the other criteria would be if "A" is greater than or equal to ">=15000" then minus "b from d" -- answer would also be in the same cell "e".
I hope I am saying my needs correctly. Basically, I have 3 different sales criteria with 3 different bonus offers. One offer is for sales less than 10,000, another offer is for the range 10,000 to 14,999 and the last is for the range 15,000 or more. I am actually linking the values from different worksheets, however I have used generically "A, B, C, D and E".
below are the 3 separate formulas that do work on specific individual rows. However, I really need the criteria for all three variables in the same cell. Right now I am just copy/paste the appropriate formula that fits the specific value in each row.
=IF(summary!$E8>15000,SUM(F3-summary!D2),0)
=IF($E4<15000,ABS(F4-summary!D3),IF($E4>=10000,0,(F4-summary!D3)))
=IF($E14<15000,ABS(F14-summary!D13),IF($E14>=10000,0,(F14-summary!D14)))
View 6 Replies
View Related
Feb 21, 2014
I have a huge document that looks like this. This is only one part of roughly 2500 lines
blank
Original Budget
Previous Estimate
Estimate Actual
Actual Costs
[Code].....
My problem is that I need to extract Soft committ and actual cost for each element (this case its TPS658). What really makes the problem for me is that I only want cost for cost place 230, 250, 300, 350 (only 350 shown here). Not the total that is shown in the row for element name.
View 4 Replies
View Related
Sep 16, 2013
I have a single column with multiple rows of data that contains multiple sports words (e.g.: football, basketball, track, hockey, swimming, skeet), and I want to find all cells that contain football, basketball and track. I also want to ignore case.
In this example only 3 rows would be found (3, 4, 8).
Sports Terms
------------
1. football, swimming, skeet
2. football, basketball, skeet
3. football, basketball, track, skeet
4. football, basketball, track, swimming, skeet
5. basketball, track, hockey, swimming
6. football, hockey, swimming, skeet
7. hockey, swimming, skeet
8. football, basketball, track, swimming
9. track, hockey, swimming, skeet
10. football, track, hockey, swimming, skeet
Here are 3 examples I found, but I do not even know where to insert them -pressing Cltrl+Shift+Enter does not do anything.
=IFERROR(IF(or(SEARCH("foottball",E2)>0,SEARCH("basketball",E2)>0,SEARCH("track",E2)>0)),"Yes",""),""),
=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")
=SEARCH((A1:A10=”football”))+SEARCH((A1:A10=”basketball”))+SEARCH((A1:A10=”track”))
View 5 Replies
View Related
Nov 10, 2008
I am trying to develope a search macro to go through sheets of a book looking for strings separated by comas in an Inputbox. For example, The data in the inputbox may be inputed as: ahu-1,vav1,erv-2,etc(Number of items may vary). Then to assign variables(range) to each in order to use the "Find" function.
View 2 Replies
View Related
Sep 28, 2011
I have a list of strings as such
A1: A
A2: B
A3: D
A4: E
How can I check to see if this range is sorted with vba?
I have searched but havent found anything for strings.
I can only think of adding each cell to an array, creating a copy array, sorting the copy array and then comparing the two arrays. I want it to return TRUE, FALSE or 1, 0
View 3 Replies
View Related
Jan 9, 2013
I have a series of incident reports (some of which are injuries), and I'm calculating the number of incidents per body part per month using:
=SUMPRODUCT((ISNUMBER(FIND($D4,owssvr!$Q$2:$Q$2188)))*(MONTH(owssvr!$A$2:$A$2188)
=MONTH(math!F$2))*(YEAR(owssvr!$A$2:$A$ 2188)=YEAR(math!F$2)))
In which D4 is the body part (Neck, for example), owssvr is the sheet with the records being summarized and F2 contains the month being queried.
The problem I'm having is that I want to calculate data for a region of the body (head & neck), which will include count any record that has a part of that body region (nose, face, eye, tooth) mentioned in the affected area text. If I simply sum all the values calculated for each body part, records that include multiple parts (e.g. "scratched nose and eye") will be counted twice.
Can I calculate whether a range of cells for the incidents contains any of a specified range of body parts (listed in D4:D15), but do this for each month, and count each record only once (e.g. "cut nose" = 1 record, "cut nose & eye" = 1 record).
View 1 Replies
View Related