I would be so happy if someone would be so kind as to help me with this one!
Cell A1 has City, St Zip
Cell B1 has 123 Main St, City, St Zip
I want cell C1 to tell me if the data in A1 is contained within B1. Obviously, it can't be an exact match because B1 contains more data. I guess after typing that out, it doesn't really matter to me if the result is shown in C1, if it changes the color of the line or some obvious distinction would be fine.
Please and thank you so much, in advance, for your help!
After many years of building Excel spreadsheets, I don't know that you can use a formula to identify partial contents of a cell, especially when the data is non numerical.
I would think the cell ref has to be whole not partial.
You can make an IF query but I'm sure it has to be the whole cell.
IF B1=(data), referring to the exact contents of the cell.
2007 GMC 3500 dually ext. cab 4X4 LBZ
Duramax / Allison Fire Red
Yes if you break it up you can do that but I am sure you have to make reference to the entire contents of the cell.
Without knowing more about exactly what you want to achieve, I can't say for sure.
You could do it with VBA by writing some code to parse each line. It could store the areas between the commas and the space after each comma, breaking down each line of text into separate fields that you can work with. From there it would be easy to analyze the fields.
Cell A1 has City, St Zip
Cell B1 has 123 Main St, City, St Zip
If you place the function
=Search(A1,B1)
in C1, the the value of C1 would be 14, the character position at which City begins in B1. If there is no match, then C1 would contain #VALUE!
Note: The Search function has an optional third parameter that tells it the character position in B1 that it should start the search. If omitted, it will start at character 1. In your situation, you should just omit the third parameter, as in my example.
On edit: I should have added, if A1 is empty, then the value place in C1 would be 1, the start position of the search. The empty string is always contained in a non-empty string.
Tom
* This post was
edited 07/24/11 04:50am by pulsar *
The FIND function is similar to the SEARCH function that Tom mentioned. Either one should work for your purpose. FIND is case-sensitive, whereas SEARCH is not.
You can set C1 to the following to display True if the text is found and False if not:
=IF(ISERROR(FIND(A1,B1))=TRUE,"False","True")
Alternatively, you can set a conditional formatting condition such as:
Rule type: Use a formula to determine which cells to format
Rule: =IF(FIND($A1,$B1)>0,TRUE,FALSE)
Format: Set the formatting you want to apply when the CSZ is found
where the text you're looking for is in A1 and the text you want to search in is in B1. You can obviously edit those cells to correspond to your spreadsheet.
Thank you, everyone, for replying. I couldn't get the suggested formulas to work, so I did break the data into separate cells and worked with it that way.
Offline
 (Custom).JPG)
