Using Cell Data Has Variables/strings For Criteria

May 23, 2007

I have a bunch of data in column A like this:


so then using this : ...

View 9 Replies


Adding Dim'd Variables: Ignoring Strings & Adding Number

Jan 13, 2007

I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.

I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.

View 9 Replies View Related

Autofilter More Than 2 Criteria On Strings With Wildcard

Mar 12, 2013

I am trying to autofilter more than 2 criteria, for example 3 criteria.

Below code works fine as the criteria are exact match strings

ActiveSheet.Range("$A$1:$O$25").AutoFilter Field:=7, Criteria1:=Array( _
"=apple", "=orange", "=grape"), Operator:=xlFilterValues

However below code does not work when the criteria are strings with wildcard "*"

ActiveSheet.Range("$A$1:$O$25").AutoFilter Field:=7, Criteria1:=Array( _
"=*apple*", "=*orange*", "=*grape*"), Operator:=xlFilterValues

I need a wildcard * indicating strings contains instead of exact match the criteria.

Data looks like this

apple, orange, peach
orange, strawberry
banana, peach
grape, peach

After autofilter, any rows that contains one of the criteria(in this case orange, apple, grape) will remain.

View 1 Replies View Related

Excel 2007 :: Looping Through Text Strings And Copying Based On Criteria

Sep 17, 2012

I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)

File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.

File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work

Option Explicit
Sub get_data_from_2()

Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range

[Code] .......

the bolded part gives me an error.

View 3 Replies View Related

Excel 2003 :: Concatenate Text Strings From Column Using Multiple Criteria Condition (formula)

Dec 20, 2012

I am looking for a way of creating the following conditioned concatenation.

I have two tables, let's call them "summary" and "detailed".

The "detailed" table is something like the following:





The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:

ID (unique)
VOL (concatenated)

V01, V03, V05

V01, V04


PS: I have people using this table with office 2003, so compatibility is necessary...

View 1 Replies View Related

Finding Strings In VLookup Value - Embedded In Larger Strings Within Table Array

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

Search For Parts Of Strings In Another List Of Strings

Dec 16, 2013

I 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.

in column A
row 1:
row 2:
row 3:
row 4:

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.

View 9 Replies View Related

Extracting Data Between Two Strings?

Feb 19, 2014

how I can extract data between two strings?

Example: My string is: -_creativename-_spongecell-_creativesize-_300x250-_creativetype-_spongecell-_

How can I create a column that contains everything AFTER 'creativename-_' and before '-_creativesize-_'?

View 11 Replies View Related

Parsing Data Strings Into Columns

May 4, 2009

I have a spreadsheet that gathers information from multiple cells and strings the data into a long string - each piece of data broken by a comma in the string.

In another spreadsheet, I copy this data string into column A on sheet 1 - starting on row 2 and down.

On sheet 2 - and this is where I need help - I would like to have a macro that will take each data string on Sheet 1 column A and parse it out into multiple columns but the same row on Sheet 2.

ColumnA Row2 Sheet1 -> changing to many columns on Sheet2 but still on the same row.

The data string always represents the same number of data components - its just a matter of breaking it out and placing each data piece into its own column.

View 11 Replies View Related

Transposing Data & Tokenizing Strings

Aug 18, 2009

I have some data in the following format:

Spreadsheet #1
Code Country Digit
0582 ALGERIA 21312 Earth
0582 ALGERIA 21313 Earth
0582 ALGERIA 21315 Earth
0582 ALGERIA 21316 Earth

I have found the following example in the forums that has helped me get to the following:

Code Country Digit
0582 ALGERIA 21312 Earth, 21313 Earth, 21315 Earth, 21316 Earth

Sub a()
'Written by Barrie Davidson
Dim DataRange As Range
Dim DataSheet As String, SummarySheet As String
Dim Digits As String

Application.ScreenUpdating = False
Set DataRange = Range("A1:C" & Range("A65536").End(xlUp).Row)
DataSheet = ActiveSheet.Name
Sheets.Add before:=Sheets(DataSheet)
SummarySheet = ActiveSheet.Name

But I need it to be in the format:

0582 ALGERIA 21312 , 21313 , 21315 , 21316, Earth

I have tried working it out myself but am a macro/excel novice (and have gone cross-eyed).

There are over 10000 lines of data so unfortunately I cant just do it manually.

View 9 Replies View Related

Change Strings Data Alphanumeric

Jan 25, 2010

I have in two cells :
a1 b1
1m 1-3-6-11-17

how can I get:


using vba code.

View 8 Replies View Related

Excel 2007 :: SUM Numbers Within Cell Based On Unique Strings In Another Cell?

Jan 11, 2012

how would you do the following in excel 2007:

"SUM numbers within a cell based on unique strings in another cell".

For example, how would i use formula to SUM the following numbers (and only the numbers for david and sam only once), 700+454+50+40+2+129+16

700 david

View 9 Replies View Related

Two Strings In One Cell

Aug 18, 2006

I have two adjacent strings in a CSV file that I'm importing which were originally intended as a single piece of data. The format of the strings are:

5'-3 3/4" (Yes, it's a good old imperial length measurement)

Right now, I have (5'-3) in one cell, and (3/4") in the next.

I'd like to try to append the second string (3/4") to the first in a single cell. Is there any way for me to do that using VBA?

View 9 Replies View Related

Search For Text Strings In Column Of Data

Mar 22, 2014

How many text strings in a reference column appear in paragraphs of text listed in another column within excel.

So column A contains text strings such as:


And I need to see whether any of these appear in cells in a reference column G. If they do, I would like to return 'Used' into column B.

An example of the type of text in each cell in column G is:

"If you have any questions regarding your offer, please contact me. For any questions regarding your benefits, payroll or company policies and programs, please contact HR. Sincerely, {{Advisor_Signature__c}} {{Advisors_Job_Title__c}}"

I don't seem to able to search for a text string across multiple reference cells.

View 3 Replies View Related

Assigning Data Range From 2 Strings Of Text

May 4, 2009

Im 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 Related

How To Find A Row That Has 2 Or More Strings In Any Cell

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

Extracting Mid Strings From A Cell

Jul 4, 2009

i have in a cell a Text like this..

Chicago (TZGUG)

i tried a formula.. like this..


to remove only ) ..

but how can i remove the ( too ..

and also Chicago

so the result in a cell would be TZGUG

View 11 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Sum Number Of Different Strings In Single Cell?

Jun 23, 2014

I am attempting to count up the sum of names that will be submitted, through a web form, in a single cell.


Cell B1 will have a bunch of names submitted to it. let's say 10: Marino, Elway, Namath, Montana, Manning, Brady, Romo, Bradshaw, Favre, Aikman. (these text strings will be all in one single cell)

I will have a list of names that are correct to compare to: Montana, Brady, Bradshaw, Manning. I can put those in its own cell to compare to. (C1)

How do i get the sum of 4 from B1 cell? If B1 only has 3 of the 10, how would it return the sum of 3?


A1 B1 (Submitted cell) C1 (List to compare to "right answers") D1 (sum from B1 compared to C1)
user 1 Marino, Elway, Namath, Montana, Manning, Brady, Romo, Bradshaw, Favre, Aikman Montana, Brady, Bradshaw, Manning 4
user 2 Marino, Elway, Namath, Montana, Manning, Brady, Favre, Aikman 3

This is I am sure a very newbie question, but I couldn't find anything on multiple text strings within one cell, and counting a sum of those.

View 14 Replies View Related

How To Calculate Multiple Strings In Same Cell

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.


View 6 Replies View Related

Selecting Specific Strings From A Cell

Oct 17, 2008

I'm using Excel 2003 and I have a small problem using text functions. My problem is that in column 'A' I have a name and their year next to it between brackets, like so: Wright Jeffrey (1PBSO)

Now what I have to do, is make two new columns where I extract the surname (Wright) to one columns and the first name in another column (Jeffrey), the "(1PBSO)" doesn't have to go anywhere. Now I wouldn't have a problem with this normally but in the list I have names like 'Van Tongerloo Johan' where VAN TONGERLOO is the surname and JOHAN is the first name. For the first name (Wright Jeffrey (1PBSO)) I just used the LEFT function and then searched for the first space in the string but that doesn't work in the second example anymore.

View 5 Replies View Related

Look For Identical Strings And Add Cell Values Beside Each

Feb 18, 2009

Hi guys, I am looking for a macro to do the following:

Go down a column and identify all accounts that are identical, then add up the values that are 5 columns to the right of each account and paste them on another sheet along with the account code (there are 12 values for each account to be added with the other identical accounts).

I've attached a spreadsheet, because I understnad this might be kind of confusing. In essence:

1. Go down column C

2. Look for accounts that are identical

3. Add up the values for each account in columns G:R

4. Copy and paste the added values to another sheet along with columns C:F for each account to identify it.

View 9 Replies View Related

Extract Date From Cell With Other Strings

Dec 22, 2008

i have data in column A and i want extract to column B only the date from this data. Date start on diferent position.

col. A
1 text text 10.10.1998 text
2 text10.12.2001
3 10.12.2004
4 10.12.2005 text text

i want this result
col. B
1 10.10.1998
2 10.12.2001
3 10.12.2004
4 10.12.2005

i try solution from other thread, but it doesnt works for me.

View 10 Replies View Related

Search For Multiple Strings In A Cell(s)

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.


=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")


View 5 Replies View Related

Extracting Strings From A Column Of Data That Match A Predefined List

Jun 15, 2009

I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.

View 2 Replies View Related

Determine Data Type Of Array Elements. Numeric Strings

Apr 7, 2008

I have an array of variants..

lvarArrSource = Array(12, "=F", 2, 3, 4, 5, 11, 6, 7, 8, 9, 10, "123", "F", "F", "F", "F", 11, "F", "NR", "F")

which i am using to map columns between spreadsheets. The basic numeric entries refer to columns to copy. But I want to make the routine smarter with the strings.

If array(x) = 12 (For example) Then
Do something
End If
If left(array(x),1) = "=" Then
Do something Else
End If
If array(x) Is String literal (e.g "xyz" Or "123") Then
Do a third thing
End If

i cant find a typeof or isstring kind of function. Isnumeric works ok for some values but quoted numbers (eg "123") return true (which isnt what i want). I have tried the left(string,1) = """ but excel seems to hide the quotes.

View 4 Replies View Related

Delete Cell Data With Criteria?

Aug 2, 2014

I have large worksheet with data. Have data in the range L121:ED490.

I need to VB macro to check cells in column L,P,T,(every 4th column) until row 490 column ED and see if the cell is blank. If any cell in column L,P,T, and so on until column ED is blank then i need to make corresponding cell in column N,R, V and so on blank. Example : If L151 is blank then need to make N151 blank. If T152 is blank the need to make V152 blank.

View 3 Replies View Related

Use Cell Data For Filter Criteria

Feb 15, 2010

I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.

View 9 Replies View Related

How To Get Data From A Cell Given A Specific Criteria

Jun 19, 2007

I have a big data sheet. I have approximately 2170 rows and 50 columns. In some rows I have a series of consecutive zeros. I need to build a formula that gives me the date ( located in row3) right before the first zero in the series of consecutive 12 zeros.

For instance in row 1340 I have a series of 12 consecutive zeros. The first zero happens in column AC, therefore I need the formula to retrieve cell AD3. however every rows has zeros located in different cells therefore I need a general formula.

View 9 Replies View Related

How To Match Number Strings In Cell Contents

Sep 3, 2013

I have a spreadsheet with 60k lines. One of the columns contains barcodes as per the example below, with a suffix of -XXX-XXX where the XXX are numbers. I want to highlight all the cells where the -XX-XX are the same number (as in the example below -303-303) how I can do this with a formula as I've around 30 spreadsheets each with 60,000 lines I need to sort through?


View 4 Replies View Related

Copyrights 2005-15, All rights reserved