Formula / Macro To Search Cell Text And Assign Value?

Jan 30, 2014

I am trying to get a code which searches each row for a keyword, and then assigns a value to it depending on what group of keywords it is.

For example, if the Name or Comment mentions certain fruit keywords, I want it to assign the value: "Fruit", and if it mentions certain vegetable keywords, I want to assign it "Vegetables", and if it is unrelated to either, I want to keep it blank. If the name/comment mentions both fruit and vegetables then I want it to say "Error".

Since the keywords I am using are mostly two letters, I want the search to only search for the two letters on its own, and not as part of a sentence. e.g. if the key word is ED, I don't want it to return a value for "I rested". Instead, I want it to stay blank.

i.e. I want the code to take information from column A and B, and assign a value based on the keywords in column D and E. As an example:

A B C
Name Comment Value
V1 Eating vegetables are good for you Vegetable
null lock out of ep. Fruit
S-1 It is JUST
Fruit-sp Error

D E
Fruit Vegetable
EP SW
KG SP
ST CM
BV LP

The formula I used so far is this: where C3 is the first value
C3=IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$D$2:$D$6,A3))),ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$D$2:$D$6,B3)))),"Fruit","")&IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$E$2:$E$6,A3))),ISNUMBER(LOOKUP(100,SEARCH($E$2:$E$6,B3)))),"Vegetable","")

In columns D and E I put a leading and trailing space between each word to only search for the keywords themselves, and not as part of a word. (e.g. ST is correct instead of haSTe).

This formula seems to work only for 60% of the information, problems occur when the keyword is at the end of comment sentence: the value is blank. e.g. in row B4 of the table I made above my formula returns a blank.

View 1 Replies


ADVERTISEMENT

Assign A Macro To A Search Button

Oct 22, 2009

I have a start page in the beginning of my excel workbook.
This is where i want to assign a macro to a search button.
I want the macro to search the entire workbook but only in a certain block on each worksheet.

View 9 Replies View Related

VBA Macro To Assign HTML Content Of Wikipedia Search To String Variable

Feb 16, 2014

I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.

Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.

View 1 Replies View Related

Search Specific Text In Column Range (Macro Or Formula)

Jun 30, 2014

I need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".

Case is not sensitive.

Words
Title
Displayed?

Christmas
Coworker has chronic hiccups
Yes

Excel
I Love Excel
Yes

Cartman
I Like Turtles
No

Ninja
Oh Christmas Tree
Yes

Tiger
Case of the Mondays
No

Chronic
Cute Monkeys
No

View 4 Replies View Related

Search Formula (return A Statement If A Certain Month Is Contained In The Text Within Another Cell)

Dec 21, 2008

I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")

Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.

At the moment it is putting in the first test for every month I select and not changing to blank.

View 2 Replies View Related

Assign Value To Text Formatted Cell

Dec 30, 2008

I want to be able to "count" apples and oranges. Is there a way to record a particular text in a text formatted cell and count it. e.g. 10 cells, 3 say "apples", three say "oranges", 4 say "plums". I want excel to keep track of the three types of fruit when I change them and give me a running number of each.

View 2 Replies View Related

How To Assign Text From Textbox To A Cell

Sep 3, 2012

I have used a textbox ( not an activeX text box) to get input from user. I want to get that value into a cell.

View 3 Replies View Related

VBA - If Cell Contains Text Within Array In Different Workbook / Assign Value

Oct 20, 2012

I have a worksheet (WORK1) that contains two columns of values that I can add to or change if necessary. The first column would be a text string (STRING), the second column would be its category (CATEGORY).

Example:
STRING CATEGORY
Chevron Gas
Exxon Gas
Vons Groceries
McDonalds Hamburger

In a separate workbook (WORK2), I have a column (A) which contains text. Ex:

A1 = "Station 2134 Chevron Motor"
A2 = "ExxonOil 234"
A3 = "Vons store"
A4 = "Hamburger McDonalds"

I would like to assign B1, B2, and so on... a CATEGORY if it contains STRING.

For this example, B1, B2, and so on... would be assigned the values.

B1 = "Gas"
B2 = "Gas"
B3 = "Groceries"
B4 = "Hamburger"

View 5 Replies View Related

Assign Macro To A Cell

Jul 21, 2009

I would like a to activate a form every time I click in a given cell. how to do this?

View 4 Replies View Related

Assign Active Cell In Macro With Dynamic Cell Choice

Dec 29, 2009

Before unloading a userform the range to select the active cell is set to

View 3 Replies View Related

Automated Search & Assign Functions

Jul 16, 2009

I have 2 worksheets - Data & Reference.
in Reference worksheet, have 2 columns - Cards & State
* Cards column contains a listed points which will be added from time to time. The points can either be a word or part of a sentence etc.
* State column contain various general category

Table Example in Reference worksheet
Cards- State
MBB Visa- Classic
CitiBank Visa- Gold
Diners- Classic
Amex Corp- Platinum
MBB Mastercards- Gold
DirectAccess Persona- Gold
CitiBank Mastercard- Platinum
Amex Persona- Gold
DirectAccess Corp- Platinum

in Data worksheet, have 2 main columns - Remarks & Refs.
* Remarks column contains all sorts of data. The rows can be more or can be less day by day.
* Refs column will auto refer to specific reference.

Table Example in Data worksheet
Remarks- Refs..................................

View 7 Replies View Related

Search Cell For Text String And Paste Text In Based On Finding?

Jun 29, 2014

I need to create a macro to do the following:

Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.

For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.

View 7 Replies View Related

VBA Macro Which Search Particular Text

Dec 17, 2013

I've 2 columns in Sheet1 (data base) whit text in first is names of keys i.e. A1,P14,M18 and in the second column I have where is the key. So I'm looking for macro which will be searching for a key from first column, but in the search result will be place where is this key (the text in second column).

View 4 Replies View Related

Text Search Returns Cell Text Contents Of Different Column In The Same Row

Jun 7, 2007

Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found.

A prepopulated worksheet has the text "gold" entered in cell T278.

1. user searches for "yellow_metal"
2. Excell finds "yellow_metal" in row 278, say in cell A278.
3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example.
4. Excell returns "gold"

View 9 Replies View Related

Macro To Edit A Cell & Convert Formula To Text

Oct 13, 2006

Have a macro that copies a formula from each of 100 workbooks to a new workbook. I want to display these formulas as text and want a macro or someway to display these cells as text. I have tried to record a macro that presses the F2 key, the home key and the apostrophe. This works for the one cell but provides the following macro that does not work for anyother cell.

ActiveCell.FormulaR1C1 = _
"'=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)"
Range("B3").Select

View 2 Replies View Related

Search Symbol And Return Text Formula Not Working

Jun 13, 2013

I have a formula to find if the Symbol "!" is contained in the a cell. And i want it to input "Undercut Due to Trim Edge" if there is a "!" symbol and blank if there is not. What did I do wrong?

=IF(ISNUMBER(SEARCH("~!",AL57:AV57)),"UNDERCUT DUE TO TRIM Edge","")

View 3 Replies View Related

Macro To Search Text & Return Values

Jan 9, 2009

I have a spreadsheet containing several names. I want a formula or macro that will search a string of text within the cell. If the text exists within the cell, I need a code to populate. For example,

In cell E2, search for a phrase anywhere in the cell that contains "COM". In cell H2 look for the exact phrase "ASB". When "COM" is found in E2 and "ASB" is found in H2, in cell U2 return the value "COM ASBTVL".

In my case, E2 may not contain "COM", it may contain "DEN" instead. In that case, if E2 contains "COM", and if "H2" contains "SVC", then in U2 populate "COM APPSVC"

I'm guessing in my code I will need to list a set of criteria that will search row by row and return certain values when finding the text I specify.

Would a macro be best for this? Does anyone know how I would put that together?

View 11 Replies View Related

Formula To Search Text From Another Sheet And Add Sum Based On Date Range

Feb 9, 2014

I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.

Columns: Name, Size, Date
nmlclex06114/12/2013
nmlclap121012/12/2013
nmlclex0651/10/2013
nmlclex0619/10/2013
nmlclex0611/01/2014
nmlclap12124/10/2013
nmlclex06113/11/2013
nmlclap12118/12/2013
nmlclap1219/12/2013
nmlclap12130/12/2013

View 11 Replies View Related

Macro To Search For Text And Paste Data In Other Area?

Apr 1, 2014

I have a report that runs, and it shows users that are in different groups. Within each group are primary users, and sometimes secondary users. Attached is a spreadsheet showing an example of the report in "A7:A52"

What I want to do is have a macro scan column A, and if a group is found, create what I have made in the rest of the spreadsheet (CD6 and below). I manually filled in the data for group 1, and would like it to continue off to the right in the same format.

There are some areas that dont have groups, such as there might be group 1, group 2, and then nothing till group 10 (as shown in the example)

View 12 Replies View Related

Macro To Search Cells For Specific Text Segment

May 28, 2009

Im sure this has been addressed before but i need a macro that would act like a "Contains" function. I have Column B that has user comments. These are sentences like "I have a billing issue" or "My item is damaged". My goal search these for keywords and then have a value (category) populate in Column C

Heres an example. the Category is Damaged Goods: Keywords associated with it are "broke" "broken" "replacement" "damaged" "not working"

Another category is Billing: keywords include "bill" "credit" "account"

I need a macro that will search all the comments and if "broken" (for example) is found anywhere within the comment, the value "damaged goods" will be populated in the same row, column C

View 3 Replies View Related

Formula / Macro To Search For A Particular String

May 20, 2009

how to use a formula or macro or whatever that is applicable to solve my current issue.

For example:

I've the following text below. The prefix of "atest", "bgo", "crun" and "dfly_c" are fixed pattern, however, the text after prefix will change accordingly.

atest_myhomeisfar
bgo_runfast
crun_b:veryfast
dfly_c:bluesky

Now my request is, I would like to have the final output as per below. Notices that there are some additional numbers behind the text (with semicolumn).

Final output:
atest_myhomeisfar:1998
brun_veryfast:2009
cfly_bluesky:1790
dfly_c:bluesky:1800

My question is, is it possible to create something to handle this issue? I mean something that will seach thru a particular fixed pattern as mentioned above and add the number behind it?

I would like to do it one at the time (one prefix at a time). Not mass searching and adding.

For example:
Search all "atest*" pattern and add an additional number behind.

View 14 Replies View Related

Formula Or Macro: Live Search Name

Jan 21, 2009

i try create formula or macro, which will search in data during writing in cell.
F.e. i have column
A
1 dano black
2 dodo red
3 phil blue
4 black jack

I want something, what will be look in this column "A" up, during my writting in cell B1 and show results in C1. When i write „b“, it will show in B1 „dano black“, if i add „l“ (it will be written „bl“), it shown again „dano black“. If i add „u“ (it will be written „blu“) , it shows „phil blue“

View 3 Replies View Related

Search Cell For Text

May 3, 2007

I'm trying to write a VBA If statement that searches the text of a cell for a specific string.

Let's say the cell contains "Bismarck State College"

I want the If Statement to search for "Bismarck" in the cell and continue with the If Statement if it's found.

Here's what I have that I found in the forums but it doesn't seem to work:

If Cells(r, 6).Value Like "*bismarck*" Then
BISM = BISM & vbCrLf & Cells(r, 2)
End If

View 3 Replies View Related

Conditional Copy Routine: Macro To Search A Column For A Specific Text String

Oct 5, 2009

I am trying to write a macro to search a column for a specific text string which when found, will copy the whole row the string is in. Once this row has been copied, I then want the macro to activate a new sheet and search for the next available empty row to paste the data. Once this has been done, go back to the original sheet and find the next cell in the original column with the specified text string and repeat until the range has been satisfied. Below is the script I have that sort of works.

View 5 Replies View Related

Search Cell To See If It Contains Certain String Of Text

Jun 13, 2014

I need to find a formula which will search a list of cells looking for a particular text string. If it doesn't find this text string it then needs to search for the next one. For instance if I had a list of product codes: ABCD1234, BCD1234, ABCD2345, CDE23456, BCD2345 I want the formula to look up and see if the cell contains the text ABCD and if so return ABCD as the value, if not I want it to go on and see if the cell contains BCD and return the value BCD, if not then go on and search the value CDE etc. It seems like an easy job to do manually but I have a list of over 3000 codes to do this.

View 4 Replies View Related

Search Using Cell Content/Text

Oct 4, 2006

need to do a search based on the contents of a cell, but this cell wil change from minute to minute, so the result of that search will obviously differ each time. So far, the relevant line of macro/VBA code is only searching for what was in the cell at the initial time of recording the macro, for example:

Cells.Find(What:="Allen", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Application.CutCopyMode = False

...searches for "Allen" each time, no matter what the contents of the cell which contained "Allen" initially, is now.

View 3 Replies View Related

Search Each Cell In Row For Text String

Jun 11, 2008

I am using VB and need to search each cell in the row for a string "U30". If it appears, then I need to grab that plus the next three digits, "U30XXX". Otherwise, I need to grab the last six in that row (=RIGHT(M2,6)). I am not sure how to structure the If statement in VB. I searched the forum and help files, but was not sure what to do from here. Currently, I have...

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[4],6)"

Range("I2").Select
Selection.AutoFill Destination:=Range("I2", Cells(Rows.Count, "I")), Type:=xlFillDefault

View 7 Replies View Related

Search For Text Within Cell Then Return Contents Of That Cell?

Jun 19, 2014

So right now I have a spreadsheet that looks something like this:

A
B
C
D

[Code]....

As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.

Right now I have: =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")

This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.

View 4 Replies View Related

Find Text In Cell (search Whole Table)

Jun 17, 2009

I am having a littler trouble with using a function to find text (last name) in a table and then return full name from the table. See attached sheet with example of what I am looking for.

A quick explanation:

Have a table with columns that have team and name of player. My input is the last name of the player and the team. Need a fomula that searches all the table and then returns the full name of player based on 2 conditions of last name and team.

View 2 Replies View Related

Search Cells And Replace Text In That Cell

May 5, 2009

Search multiple cells for certain string and then replace the text of the entire cell with new text. For example the text may contain:

"A Chestnut Leather Satchel Binocular Case "They'll Fight Over When You're Dead" (Binocularcase-SL-CH)"

I want to search for SL-CH and once that is found I want to replace the contents of the entire cell with the following: Satchel Chestnut (SL-CH)

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved