Text Configuration - Tell A Cell To Pick Up A Phrase?
Aug 15, 2013Text configuration - how can I tell a cell to pick up a phrase from one cell, but if that phrase is equal to one particular phrase, then produce a blank.
View 3 Replies
ADVERTISEMENT
Identifying Phrase In A Text List
May 14, 2014I have a cell with a long list of phrases all separated by commas.
So it would appear, chicken, beef, steak, soup, fish
I want to create a rule in a cell to identify if one of those phrases comes up - so if chicken comes up then a 1 would appear.
To make it interesting is it possible to grab two words from the cell, so grab the words chicken and beef from a mass of words in one cell and bring it across.
I have these cells which contain huge amount of text and preferences and I am looking to identify these entities on if they contain these words.
Count Up How Many Times A Particular Phrase Within A Text String Appears
Feb 7, 2007i have a spreadsheet where I need to count up how many times a particular phrase within a text string appears. The text string will be duplicated many times throughout the spreadsheet.
For example :
Miss X was at work on Saturday
Mr XX was at work on Saturday but not Tuesday
Miss Y was at work on Tuesday
So I would like to count up how many times "work on Saturday" appears in my spreadsheet, and then as a seperate query, how many times "work on Tuesday" appears.
Analyze Cells Of Text-return Defined Text Phrase If All Cells Have Same Text
Apr 30, 2014I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies View RelatedFind Phrase And Look Up Corresponding Cell Value
Mar 24, 2009I have three columns.
Column 1: A list of items with a very long text description in each cell
Column 2: The Key phrase found within the long description in the cells of Column 1.
(The key phrase is different for each cell).
Column 3: Custom shortened description in text format.
Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1
How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?
PC Configuration
Dec 17, 2009I currently use a Core2 Duo PC and often max out the both CPU for extended periods when recalculating large (30MB+) spreadsheets.
Wondering if anyone has experience with Quad Core desktop PC's and large files and if there is a noticeable / significant performance improvement with more cores..
Ram doesn't seem to be an issue.
Function To Pick Out Percentage From Text String?
Dec 2, 2013In the attached workbook i need to take out the % numbers from the text to populate the cells.
the Pay % cell is the % before the / and the receive % is after the /.
Identify Number Pattern / Configuration
Jan 21, 2008What I want to do: find the four cell that have the same value "99" in belows configurations
A B C D
1 99
2 99
3 99
4 99
A B C D
1 99
2 99
3 99
4 99
Formula To Pick Out Text In Middle Of String And Return
Jan 20, 2014I am looking for a formula to remove the text 2.2 from the string Rav4 2.2 D-4d X or 1.33 out of Yaris 1.33 Vvt etc, I have tried using the formula
=IF(P2="","",VLOOKUP("*"&P2&"*",AM$1:AN$25,2,1))
But it just keeps returning #N/A. I have all the variables outlined in cells AM$1:AN$25 hence the reference. I currently have the formula
=IF(P2="","",VLOOKUP(P2&"*",AK$1:AL$25,2,1))
Which is working perfectly @ finding and returning the start of the string but just cannot get the one to find the middle characters.
Pick Folder & Import Multiple Text Files
Oct 17, 2007Import Multiple Text Files & List Names
I would like to use FileDailogFolderPicker object to prompt the Excel User (me!) to browse to the location of the folder which is required to execute the code below; at the moment I have to change the file path in the VBA code each time I run the macro. The code is run on a Click Event button at present.
Private Sub GetGPCFiles_Click()
test
End Sub
Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String, a()
Dim x, i As Long, n As Long, b(), t As Long
myDir = "Y:DCCRSP07RSPWE191007131007GPC" 'Have to input this path manually
fn = Dir(myDir & "*.gpc")
Do While fn <> ""
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, ",")
n = n + 1
Redim Preserve a(1 To n)
a(n) = x
Loop
Worksheets Are Not Saving Correct Protection Configuration
Jan 18, 2009I have a workbook with several worksheets in it. 4 of them have macros written in them, some do not. I protected each sheet with a password and made it so that users couldn't select any "locked" cells, and did this on ALL the worksheets. I noticed now that, just upon opening the workbook (not running any macros yet), on the sheets with macros I'm able to select locked cells (which is wrong), but the sheets without macros still function correctly. I think that this may have to do with Excel's default protecting arrangement where you can select either locked or unlocked cells. I specifically locked all worksheets with the correct arrangement but every time I open the workbook some of the worksheets default back to Excel's default arrangement. What can I do about this problem? I need this configuration to follow the workbook and not the specific computer it's on. Many users are going to use this, and can't be able to select locked cells. My macros do unprotect and re-protect the sheets to run, and I'd wonder if I can edit these to protect the sheets in a different configuration (which I don't think I can do with regards to being able to select locked cells) but these macros don't even run before this happens, and the worksheet open event doesn't involve any unlocking or re-locking (it just disables the cut and drag-and-drop functions.
View 8 Replies View RelatedCreate Function To Choose And Display Optimal Configuration Of 5 Box CBM
Feb 27, 2014I am creating a box CBM optimizing tool at work for shipping our products internationally so that the sales department and logistics can estimate shipping costs and weights with out needing me to physically pack everything in advance. I have broken every product we sell into individual CBM's as well as the boxes that we use to ship. I have also set it up to give a break down of "boxes needed" to accommodate the approximated CBM by box size.
How to create a function that will take that info and calculate which combination of boxes will utilize the available space... In other words, how do i take my break down of the 6 box sizes available, with the "# of boxes needed" data i already have, and have it tell me which combination of boxes will utilize the maximum percentage of space, or leave the least amount of unused space based off of the cumulative CBM being shipped.
Snippet attached:
Capture.PNG
Sumproduct? Sumif? (search A Range Any Cell Containing Words Or Phrase "20%" )
Jun 4, 2009I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
Pick Cell Based On Multiple Cells
Feb 5, 2009I want the 'Model Number' to automatically be picked based on the numbers entered into Sheet1 B1 & B2. The model numbers are listed on Sheet2.
View 4 Replies View RelatedVBA Code Required To Pick Value In Upper Cell?
Feb 25, 2014I want a macro fill the value which is on the upper cell.
eg:If A1= Code and A2 is blank, A2 should be filled with A1 value which is 'Code'.
In the attached, I want C3,C4,C5 to be filled with 'A' which is in C2,
likewise I want C7,C8 to be filled with 'B' which is in C6 and C11 to be filled with 'C' which is in C10, likewise the data has to be filled in entire C col.
Make Formula Pick Up Filename From A Different Cell...
Feb 9, 2010I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32
There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.
List Box Of Ranges, Pick One To Activate That Cell
Mar 30, 2007How can I add multiple cells (ranges) to a listbox, and when the user clicks one, it will activate that cell in excell. I thought about have the list box for the text, and a corresponding array that holds the actual range or address, but I cant get it working.
Long version:
I have a search feature that, when you type a name, it searches through a column and finds the name. Now if there are multiple matches to your string, I want it to add the full name to a listbox, and have the user pick one from the list to activate.
So basically, if I search for the name "john" but theres multiple people with that name ("john doe", "john smith", & "john mazz"), it would add the three to a list box, I would pick one from the list, and it would activate that cell.
I have everything done except that last part. I have it adding multiple matches to the list box, but I cant figure out an easy way to activate the correct cell when double clicking the item in the list. I could have it search again for the selected full name, but there is likely to be a case where one person is entered twice.
Pick From List Or Combo Box And Return Definition In Cell To The Right..
Jan 18, 2009In my attachment, I have a worksheet where I need to pick a rating from a combo box or list (Expert, Leading, Applying, Learning, Under Performing). I will be picking this rating 10 different times on this worksheet and the selections will not be the same in all cases so the definition I need won't always be the same.
I would like to return the definition (definitions are detailed on another worksheet, Data Elements) to the cell to the right.
I've tried IF statements but when I make one selection from the combo box, all the combo boxes select the same thing. I've also tried vlookup but can't figure it out and can't find an explanation in layman's terms.
Pick Data From A Specific Row/column (eg 10/B) Related To Active Cell
Dec 2, 2009I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
Get The Last Word In Phrase
Nov 4, 2006I'm doing a macro to get the last word in a phrase
View 7 Replies View RelatedMacro That Deletes Row Before And After Certain Phrase
Nov 3, 2008I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
So for example,
(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below
If the macro finds the text in column 2, it would delete 1, 2 and 3.
I need to have a second macro that performs a very similar function.
it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.
Seperating Parts Of A Phrase Using Vba
Aug 3, 2009I have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"
I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State
Is there any way to do this within the code?
Phrase Search Macro
Jan 26, 2014I would like to write a macro which checks if phrase from column B (lets say 13845) can be found in column A cells.
For example DDH113845G389.
I think that it should be some kind of loop but I'm afraid I can't handle it.
Extract Characters From Phrase
Nov 15, 2006i have a list of information, and from that i want to extract a certain piece from that information and out it in a new cell...
eg. 'KLM00506', 'KLM00409-10', 'KLM00821-9' etc
as you can see, the info is not in the same length
what i need is to take out the FIRST 6 characters and have the remaining to be in a new cell
anyone has an excel formula that i can use ? not really interested in code, cause in this particular worksheet, everything is at its' place, no code required
Count Certain Phrase When There Is Multiple Answers
Jun 4, 2014In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
HRA Results.xlsx‎
Rank Instances Of Phrase In A Column
Mar 21, 2014Let's say this is my data. It's already been sorted so that like entries are all grouped together.
Ex1
Ex1
Ex1
Ex2
Ex2
I need to make a macro that numbers my data like below.
1
Ex1
2
Ex1
3
Ex1
1
Ex2
2
Ex2
Essentially, my data is many columns and sorted on a bunch of different levels. I need a quick way to assign a rank to every row in the same group, to see at a glance which performed the best.
VBA Code For Find And Replace A Particular Phrase
Apr 23, 2008I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.
So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.
Worksheets(1).Range("B2:B50").Replace What:="1.ValueAddedProcessing", Replacement:= _
"Sheet1!A1:A11", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Search For Only One Word / Phrase In Range Of Cells
Jul 23, 2014I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.
For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".
Search For Keyword Or Phrase In Website Using Excel?
Apr 11, 2012I would like to search for a keyword or a phrase in a website using excel.
Generally we copy the keyword or a phrase and paste it in google and go for search. but i wanted to do this using excel as i have to search same pharse in multiple websites.
How To Count Number Of Times A Phrase Is Repeated In A Row
Jun 24, 2012How I can count the number of times each unique phrase in row "A" is repeated?
For example if my data set was
Blue
Green
Black
Green
Red
Red
Red
Red Hat
how can I get excel to count the number of times and return data like
Blue 1
Green 2
Black 1
Green 2
Red 3
Red 3
Red 3
Red Hat 1