Criteria To Match Part Of String

Dec 3, 2008

I have a worksheet that I need to filter on 8 columns. I need to do this in the form (Col A = Bob OR SAM) AND (Col B = Apple OR Pear OR Banana) AND... etc for 8 columns.

I started this thread and we reached the conclusion that I needed either a macro to list out all the possible combinations on separate lines, or a complex criteria formula.

I'm trying to put together the complex criteria, but I am further stumped by the fact that some of the columns contain multiple terms with comma delimiters, so I'm trying to match part of a string, rather than a whole string, so the usual = ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A:$A,0)) won't work. I tried using =ISNUMBER(FIND(Sheet2!$A:$A,Sheet1!A2)) instead, but I think this fails because it should be an array formula. I tried normal entering and CSE entering, and neither work, so I think this is a dead end.

View 9 Replies


ADVERTISEMENT

Evaluate Part Of String To Match Two Conditions

Sep 24, 2006

I m trying to evaluate part of a string to determine if a number and if 6 char's in length. If Yes, return the 6 digit number, else 0

My formula below returns a #VALUE! error
It worked before I added the 6 char length condition, but sometimes return values of greater length that I do not need.

=IF(AND(ISNUMBER(VALUE(LEFT(C1809, FIND(" ",C1809)-1))),LEN(VALUE(LEFT(C1809,FIND(" ",C1809)-1)))=6),VALUE(LEFT(C1809,FIND(" ",C1809)-1)),0)

View 5 Replies View Related

Delete Rows If Column Has Fewer Than X Matches To Part String Match

May 5, 2008

I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.

Data set example:

In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.

Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
.AutoFilter

I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.

View 6 Replies View Related

Remove First Part Of String Of Text And Keep The Second Part?

Feb 12, 2014

Is there any way to remove the first part of a string of text in a cell and save the second part?

The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.

The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?

View 5 Replies View Related

SUMProduct (182 Matches Part 1 Of Which 32 Match In Part 2)

Nov 18, 2008

Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).

=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))

View 3 Replies View Related

Pull Out Part Of A String?

Oct 18, 2013

formula that will pull out the number preceding the * 60 in the text below? In this particular case I would be looking to get 5 as the result. That number could be any number but in the example below it happens to be a 5.

Channel Tune Duration in Seconds} (ID) >= (5 * 60))

View 4 Replies View Related

VLOOKUP And SUM On Part Of String?

Apr 13, 2012

My sheet contains values like these:

A/5
A/3
B/4

What I want to do is sum all the values after the / for a particalur value before the /:

A 8
B 4

I already tried VLOOKUP, but this function wants you to point to a certain cell to return instead of a just a part of the cell. It should return 5 for the first row to be able to perform a sum for every value of A.

How can I perform such a calculation without having to copy the values to separate cells? Can I contain all of this in one function?

View 5 Replies View Related

VLOOKUP For Part Of A String

Oct 4, 2012

Im sure this cant be done because it defies the logic of the LOOKUP function but I know there are some clever people on here so i thought id ask!!

I have 2 workbooks containing similar data from 2 different sources; the one common data item in both is a clients address but have the age old problem of data quality, which needs sorting eventually but i need a quick fix!

In workbook one for example the address will be recorded as RUSKIN RD

In workbook two the address will be recorded as RUSKIN ROAD

A simple look up wont work obviously as the 2 values dont match, then i thought of using the LEFT function but again this goes against the logic of the VLOOKUP.

View 2 Replies View Related

Remove Part Of String

Oct 29, 2009

I have a list of data that populates B2:B2900
This data is often prefixed by a 'reference code' that I wish to be removed.
Now rater than perform this manually aprox 3000 times is there a formula or some VB code that will complete this for me....

eg

The list of data is shown like:

SC7547-05 - Payne, Freda
SC8706-08 - Rungren, Todd
SC8714-05 - Travis, Randy
SC7517-03 - Beach Boys, The
Love Song
Now You're Gone
SC7512-01 - Horton, Johnny
SC8721-15 - Journey

So I wish for the SC7547-05 - to be removed from the first example to just leave Payne, Freda and continue this throughout the list
However if the data is found not to include this code (as in the 5th/6th examples above) leave it alone

So if the code of formula is run for the above the outcome would be....

Payne, Freda
Rungren, Todd
Travis, Randy
Beach Boys, The
Love Song
Now You're Gone
Horton, Johnny
Journey

View 9 Replies View Related

Vlookup Part Of String

Sep 29, 2006

I'm trying to use this formula to lookup the value in the array that matches, however, the first 4 numbers aren't known (thus why I'm doing the lookup) =VLOOKUP("*"&C2, 'KLIF Data'!A:C,1,0)

The * is being interpreted as a character, is there a way to perform the Vlookup function using a wildcard? Or just ignoring the first 4 characters of the cells in the array would work.

View 7 Replies View Related

Return Part Of A String

Mar 15, 2007


TX_jhfft_DATA.05-32_65 TGIF

I need to take out the number in the middle, from the period to the space. The number varies in length so just using the mid function doesn't work. Does anyone know how to modify the mid function to return all chars between the period and the space or is there some other function that may work?

View 9 Replies View Related

Vlookup To Search For Part Of A String?

Nov 17, 2008

If I have a store number (say 1234) and I am trying to search for something which contains 1234 (i.e. the whole string of ABCD1234, ABCD1235, ABCD1236 etc) is it possible to do this in one VLOOKUP formula?

One way of doing it would be to VLOOKUP("ABCD"&"1234",....) but I would rather do it the other way around, to prevent the formula from not working.

View 4 Replies View Related

Changing Colours Of Part Of String

Feb 22, 2013

I have a conditional formula which gives a value based on what the User specifies.

Example of formula is:

=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))

If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then
- word MANDATORY (not the whole string) should be in RED
- words (FINANCE ONLY) should be in BLUE
- remaining string should be left in BLANK

Not intending to use VBA. Is this possible?

View 10 Replies View Related

Regex Evaluate Part Of A String

May 19, 2014

I am currently working on creating a dialogue which will allow the user to input a range of worksheets to use for a pivot table.

I want the user to be able to input a variable into the dialogue. For example, I want the input "n-5 - n-1" to mean the 6th from last to the 2nd from last sheets. I am trying to find n - ([0-9]+)? and replace it with Worksheets.Count - $1. In other words, if there are 10 sheets, I want to transform "n-5 - n-1" into "5 - 9".

Here is the code I have so far:

[Code] ....

This has been working for strings like "n" or "n-4" but not "4 - n-3" or "n-5 - n-1".

Here is the code I use afterwards to interpret the string once the n's have been replaced:

[Code] ....

I'm new to Regex and VBA in general.

View 6 Replies View Related

VBA To Delete Part Of Text String

Aug 15, 2014

I am trying to delete part of a text string for a range of cells. The code I have so far deletes the first set of letters but is looking in every cell in the range.

Ideally I would like it to only look in the cells of col B where there is data then only delete the first 10 letters if the cell begins with "Service ID"

Sub DeleteServiceID()
Dim c As Range
For Each c In Range("B1:B1000")
c.Value = Right(c.Value, 10)
Next c
End Sub

View 4 Replies View Related

Find Part Of String And Delete

Feb 25, 2009

Find part of string and delete. I have the following type of string:

View 4 Replies View Related

Copy Numeric Part Of String?

Aug 23, 2012

How do I copy the numeric part(11 Digits) of a cell

EG. A1 = "GL CREDIT DDA07 60291627599 ZFT"

I Require "60291627599" in A2

Descr
ANSWER

GL CREDIT 62180577391@DDA ZFT
62180577391

GL CREDIT 62300192854 62300192854 DDA06 ZFT
62300192854

GL CREDIT ACC REC 62273991367 ZFT
62273991367

The numeric 11 digit number is not always in the same position in the cell.

View 2 Replies View Related

Copy Numeric Part Of String

Aug 23, 2012

How do I copy the numeric part (11 Digits) of a cell ,

E.G. A1 = "GL CREDIT DDA07 60291627599 ZFT" in only want "60291627599" in A2

Descr
ANSWER

GL CREDIT 62180577391@DDA ZFT
62180577391

GL CREDIT 62300192854 62300192854 DDA06 ZFT
62300192854

GL CREDIT ACC REC 62273991367 ZFT
62273991367

11 digit not always in same position in cell.

View 7 Replies View Related

Get Numeric Part Of Alphanumeric String?

Jan 3, 2013

I have a string that with spaces in between.

example:

X3PP2 1200 U

I would like to extract the "1200". How can i do this in VBA?

View 4 Replies View Related

Removing Part Of Text From A String

Feb 11, 2013

I belong to a fantasy cycling league and after every race, I copy the race results to award each rider their points for the day. The site I visit most frequently lists the riders results in this format:

1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00
2. Lance Armstrong (USA) Team Livestrong etc... 4:06:00

I usually copy the results to my spreadsheet then calculate each riders points.

The'results end up looking like this in Excel:

1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00
2. Lance Armstrong (USA) Team Livestrong 4:06:00

So, the data ends up in 3 cells. The position in one cell, the rider and their nationality and their team in another, and the time of arrival in another cell

Is there a way in Excel where I can get rid of or delete all the data after a rider's name?

I usually end up pasting the rider information to Word, then replacing the (***) with a tab symbol, then pasting back to Excel and get rid of the data that's after the name by deleting the cells to which that data was transferred.

Is there an easier way to do this in Excel, or a macro I can create?...

View 9 Replies View Related

Vlookup Part Of The Text String

Nov 17, 2006

Is it possible to lookup if at least 8 characters of a text string is found in another text string?

View 9 Replies View Related

VLOOKUP Part Of A Text String.

Nov 17, 2006

Is there any way I can Vlookup for just part of a text stiring in a cell.
I am trying to vlook if a part number contains the letters “HFR” if it does I want
It to refer to the comment I have made in the second column of my vlook table against HFR.

View 9 Replies View Related

Parsing A Specific Part Of A String

Dec 10, 2009

I have code that retrieves the body of an email. I need to parse out certain parts of the text. For example, the text will look like the following;

LastName: John Doe
Email: johndoe@aol.com
Cell#: 555-555-5555
FileRequested: xxxxx.xlsx

I have the code to find where the specific item, ie LastName, starts in the whole text. I need to retrieve everything to the right of the : before the CRLF. That's what Im having trouble with.

View 9 Replies View Related

Extract Numeric Part From The String End

Sep 27, 2006

Extract Numeric Part From The String End with a macro, that J8 will turn 8, and R4 will turn 4.

View 9 Replies View Related

Rename Part Of String From Combobox

Sep 29, 2006

I have a Userform (PictureViewer) with a.o. a combobox and a textbox on it. The combobox has a picturename with the full path, i.e. C:My DocumentsJohnMy PicsSummer VacationImg_0001.jpg. The textbox will have the replacement name for that picture entered in it while the picture is visible, i.e. Harry and Antoinette (no extension). Pictures get cycled on this Userform with a forward and backward button. When I click the forward button, the next picture opens up and the name of that picture replaces the previous name in the combobox. What I would like to do is on the click of a commandbutton, copy the string (full path) in the combobox to a column (A), starting at the first cell and in the column beside it (B) end up with the new picture name including the full path. In this case, that would be C:My DocumentsJohnMy PicsSummer VacationHarry and Antoinette.jpg. The next picture name I want to change would be in the cells below this. This could go on for many pictures. I have the macro to change the file names once when I have cycled through everything I want to change. Is it possible to do this in one click or do I have to split the drive name, all folder names and picture name, replace the picture name with the new name manually and concatenate it again? The depth of folders can vary of course. The macro for renaming is as follows

Sub RenameTheFiles()
Dim OldName As String
Dim NewName As String
Dim LastRow As Long
Dim I As Long
LastRow = Range("A65536").End(xlUp).Row
For I = 1 To LastRow
OldName = Range("A" & I).Value
NewName = Range("B" & I).Value
Name OldName As NewName
Next I
End Sub

View 9 Replies View Related

Extract Part Of Text String

Aug 10, 2007

CREATE TABLES LIKE BELOW?Column 1 Column 2
8 6CSS130 + 2CSS200g x10hanger 200 GM
250ml B2G1 Towel in bagx24 0607 250 ML
18 g-Mint 18 GM
100g 100 GM
150GM 150 GM

i have a data as above, i wanna to extract the 250ml from the column 1, only 250ml (basically i want to extract the size code like 150g, 100gm from the data above).

View 9 Replies View Related

Match Any Part Of The Field

Jul 6, 2009

I have a large amount of numbers I need to check for in an excel spreadsheet but the problem is they may have other 'text' and other information surrounding it. For example global sub account = '8154','8111' so if I was looking for 8154 I would want to get a hit on that particular field based off a look up of sorts.

View 3 Replies View Related

Match Part Of Full Name

Apr 24, 2012

I am looking to get a user's email address.

On Sheet A (Foreseen Absences) I have users enter their own data.
On Sheet B (email addresses) I have users' full names and email addresses.

I have the following formula entered on Sheet A to get the user's email address: =vlookup(C173,'email addresses'!A:C,3,0)

This works fine for staff who enter their full names, but I've got a few who are entering things like: G. Thoms or Marie which results in a #NA error.

Is there an easy way to update this formula to account for these instances?

View 1 Replies View Related

If Cell In Col. A Exists As Part Of String In Col.B Then Color It

Dec 29, 2009

i have two columns in my worksheet A and B. Now col. A contains of words and column B contains of long strings. I want to make those parts of col. B which match to the content of A.

For Example It is now:

Col. A...................Col.B
word....................öladföljadfwordpapdfadlk
word2..................öadsfoewordpasdfword2rs

Should be:

Col. A...................Col.B
word....................öladföljadfwordpapdfadlk
word2..................öadsfoewordpasdfword2rs

I am attaching this example as excel file. I will be very thankful if some one could add the formula to this file and upload it.

View 4 Replies View Related

Delete Middle Part Of Text String

Mar 30, 2009

I am looking for a formula to delete information from the middle of a text string. I have ~ 1,000 lines of data. Each text string is composed of the same way. The data I am looking for is two parts of the string (1) all the text up to and including the first “%” character (2) the last six characters.

Raw Data
CARDINAL HEALTH 5.5%13CARDINAL 5.513 CARDINAL HLTH 5.5 061513
UST BILL 0%09US TREAS BILL 0%09 UST BILL 0.0 092409
WORLD SAVINGS 4.125%09WORLD 4.125%09 WORLD SVGS 4.125 121509
JP MORGAN 5.375%12JP 5.375%12 JPMORGAN CHS5.375 100112

Desired Result
CARDINAL HEALTH 5.5% 061513
UST BILL 0% 092409
WORLD SAVINGS 4.125% 121509
JP MORGAN 5.375% 100112

View 2 Replies View Related







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