Combining Strings In A Range
Sep 16, 2009I recently had a query about pulling data from different closed workbooks using a concatenate to easily choose the file:- ....
View 6 Replies
ADVERTISEMENT
Combining Strings Of Numbers And Ordering Them
Jan 26, 2007I am trying to come up with a formula which would combine two groups of numbers, put them in numberial order and remove duplicate numbers:
3 6 10 22
1 3 5 9 10 13 19
The above two groups of numbers are in separate cells in two different rows.
the result would also be in a different row: 1 3 5 6 9 10 13 19 22
Combining Cells (strings With Spaces)
Nov 21, 2013I have the below data that I need to concatenate, merge... I'm not sure.
The data looks like this currently:
So that it reads in one sentence, e.g. Northumberland; Newcastle; North Tyneside; South Tyneside (note - no "." or ";" at the end of the string).
I had come up with this formula -
=IF(A11>0,A11&"; ","")&IF(B11>0,B11&"; ","")&IF(C11>0,C11&"; ","")&IF(D11>0,D11&"; ","")&IF(E11>0,E11&"; ","")&IF(F11>0,F11&"; ","")&IF(G11>0,G11&"; ","")&IF(H11>0,H11&"; ","")&IF(I11>0,I11&"; ","")&IF(J11>0,J11&"; ","")&IF(K11>0,K11&"; ","")&IF(L11>0,L11&"; ","")&IF(M11>0,M11&"; ","")&IF(N11>0,N11&"; ","")&IF(O11>0,C11&"","")
Which works fine if there is a value in cell O, but if not, then a semi-colon appears at the end of the string. It also seems incredibly clunky.
Basically, I'm struggling (being a total n00b) to get the semi-colons in the right place, blank cells to be skipped or not included, and for there to be no semi-colon after the last value.
Finding Strings In VLookup Value - Embedded In Larger Strings Within Table Array
Dec 20, 2012I 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.
Separating And Converting Strings To Range
Nov 10, 2008I 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 RelatedCheck If Range Of Strings Is Sorted?
Sep 28, 2011I 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
Look For Various Strings Within Range Of Cells Plus SUMPRODUCT
Jan 9, 2013I 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).
Search Range For Part Strings
Aug 21, 2008I am using the following code to search a database of information and then display it on a different sheet.
The user types the search term into a textbox and then presses a command button to search the database.
Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?
Sub SearchDatabase()
Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range
On Error Resume Next
Passing A Range Of Strings To Vba Variable
Apr 3, 2007I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.
That's how I'm writing: ...
Compare & Identify Strings In Range
Jan 12, 2008I am having no success using either: 1) COUNTIF with wildcard (*) or 2) Case Statements to find text strings in a cell. I've even tried using Copy/Paste Special values to change the way Excel 2007 interprets the field.
The Do While loop looks at target cells in Col. 18 which contains one or more specific last names. I want to put a text string (also a name) in Col. 19 IF the target cell contains the last name in the COUNTIF or Case Statements. The COUNTIF normally puts a TRUE or FALSE based on the condition, but I'd like to put the the last name based on which condition is met and then continue looping to Row 3. It's a small number of rows, less than 100.
Here's the Case statement ...
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]....
Assigning Data Range From 2 Strings Of Text
May 4, 2009Im new to Macros. Im trying to find a string of text assign that to be the top of the data and then find a different string and assign that to be the bottom of the data. Then run a loop whilst inside that data range. Am i going about it the right way? Attached is a sample data file.
View 4 Replies View RelatedFind Word In Range Cells Containing Other Strings
Aug 18, 2008I 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.
Changing Case Of Text Strings Within A Range
Mar 2, 2007I receive data from different sources and one column is usually in upper case, but I prefer to use proper (title) case. The problem is that the data frequently contains text strings that should remain in upper case or should combine upper and lower case letters. Examples follow:
Data received:
COROLLA AE90, AE92
COROLLA SX, GTI
Data should look like:
Corolla AE90, AE92
Corolla SX, GTi
Note that 'GTi' is upper case 'GT' and lower case 'i'.
I've set up a spreadsheet 'CaseConverter.xls' as a look-up, with a range ('SpecialCase' - A2:A65536) featuring names, codes and acronyms represented correctly. Is it possible to use a macro to run Excel's global replace to search for upper case strings in one spreadsheet and replace with the correct strings from the other spreadsheet? The macro would have to loop through the entire 'SpecialCase' range and look in the supplied spreadsheet for versions of the data in the special case range without being case-specific. The supplied spreadsheet (see attached for example data) is called 'Application.xls'. Column B is what I'm trying to reach.
Extract Text Strings Within Brackets From Range
Apr 12, 2008I have a column of data in the following format:
Hardware (somethingsmall) otherinfo
Hardware ( somethingdisplaced)
Blackbox item (somethingelse) other info
Service(aservice)
etc
I need to extract the data within the brackets allowing for additional spaces for use in a second query which will search for each individual line item in turn.
For some reason the query does not like searching for the raw data in it's current format so I'm trying to extract the key information for searching instead.
I can do it in excel by:
(Assuming data is in cell V36)
'in colum x
=FIND("(",V36)
'in column y
=FIND(")",V36)
'in column z
=MID(V36,X36+1,Y36-(X36+2))
in column aa
=IF(LEFT(Z36,1)=" ", MID(Z36,2,20),Z36)
I have tried doing this cleverly in VBA and failed and then ended up trying to enter this code in the cells within the VBA code and do it that way but I'm having difficulty in doing this also.
Count Strings/Text Within Date Range
May 2, 2008I am very comfortable with VBA but I usually avoid using formulas if at all possible (unfortunately, I have, formulas are unavoidable (by request).. so formulas it is)
I will try to explain
Column A is a list of dates (ever expanding)
Column B is a list of names ( multiple entries of various names but aligned with various dates)
Column H is a list of names (each possible name which can appear in column B, no duplicates)
Cell I1 = A Date
Cell J1 = A Date
(forming a date range)
Basically what I need to do is:
= COUNTIF(B:B,H2) - but only count it if it is withing the date range (>=I1 & <=J1)
I have tried wrapping it in an IF statement but I can't seem to get it to work.
Combining If, Offset And Range Of Cells
Feb 13, 2007i need to get an equation correct for a scheduling application
assume $f24 is a task frequency and U24 is our current cell location
=IF(OFFSET(U24,0,(-$F24+1),1,($F24-1))"","","X")
i am asking if (the range of cells to the left of current cells (range being task frequency -1 columns wide) are empty - and if so - put an x - if they are not empty - then leave it balnk.
i can get it to to work for a 2 weekly schdule but need it to work upto 52 weeks
i have a simple spreadsheet with the problem clearly defined for anyone who can help
- beer in it for anyone who can help - or can pay via paypal if required
Search For Parts Of Strings In Another List Of Strings
Dec 16, 2013I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
Count Frequency Of Times Variable Strings In A Range
Nov 17, 2012I have a list of names in C1 down that I want to count. There could be hundreds of names in C1 and I don't want to sumif all of them. There must be a simpler solution
View 1 Replies View RelatedFind Specific Account Value From A Range Of Text Strings
Jan 6, 2014Need 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
Extract Strings From Range Of Variable Length Text
Jan 18, 2008I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
Combining A Data Range From Multiple Workbooks?
Jun 16, 2014I am trying to create a macro to gather a data range from multiple files placed a folder and combine them into a single worksheet which can be easily totaled. I've used some similar code I did for another project to gather the data. It starts by listing the file name and then the data set (About 40 cells) below the file title. However, the data sets from each spreadsheet are filling themselves into a single column one after the other, whereas I would like to have them populate one worksheet's data in each column.
[Code]......
SummaryWorkbook.xls
Formula For Counting Number Of Occurrences Within Range Of Cells AND Strings
Jan 14, 2014I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
=COUNTIF(D$5:D$8,OR("a*","b*","c*"))
Count Amount Of Cells Within Range That Contain Multiple Strings Of Text
Apr 22, 2013I'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
Excel 2010 :: Replace Function Missing Strings In Merged Range?
Jun 11, 2014I've recently been making a macro in visual basic that loops through all my excel files and replaces an old company name to a new company name and It's working great, well except for one thing... It always misses one string in the file and it leaves me with 90% of the file corrected. I think that the string might be a part of a merged range so Range.Find is not able to find it. I was suggested to use this code:
[Code] ......
Because that's the code that is generated when using the replace function in MS Excel 2010, but I keep getting syntax errors?
Counting Number Of Times TA Shows Within Strings Of Text In A Range Within Sheet?
Dec 7, 2011How would I go about counting the number of times TA shows within strings of text in a range within a sheet. Example: TA,MH in cell A2, CB,TA in cell C40, ES,TA in cell Q19. Result would be 3. Ideally, I'd like the formula to reference a cell that has TA as the look up data such as in A1 I'd have TA.
View 4 Replies View RelatedVBA Code To Change Font Colour Of Cell Referenced Letter Strings Within Range Of Cells
Jun 13, 2014I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
Is there a way to do this with the VBA code.
Lookup Part Text In Range In Cells Holding Text Strings
Sep 27, 2006I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
SUMIF When Cells Contain Strings Found In Range Of Cells?
Jun 20, 2014I'm looking for a function (I'm thinking 'SUMIF') that will search through column A of Sheet 1 and add column B of Sheet 1 when the cell in column A contains one of the strings listed in column A of Sheet 2. I can only find information when searching for single strings or cells.
View 8 Replies View RelatedComparing Strings ..
Mar 30, 2009I have two string that I want too compare.
String 1 could be something like channel_0_user_defined._system
string 2 could be channel_0_user_defined
I want too compare string2 with string 1 at the left side of string 1.
I don't really know what I should use I found startswith, but vba doesn't have this particular function.