I have 1 or 2 questions, depending on solutions.

I have data like 001.1, 003.1, 005.1... in cells. Then I have cell with data like "001.1|005.1". What I want to do, if it is possible - I want to compare these single values with unseparated values and if some of the single values is included in that string, then highlight it.

One possible solution (which is not what I really want, because of one extra row of values) is to do Text To Column, with separator "|" and then with conditional formatting compare that single values with that result of TextToColumn.

My question is: Is it possible somehow, to compare that single values (E2 = "001.1" etc.) directly with that unseparated values (E11 = "001.1|003.1")? [This is my prefered solution]

Or if NOT, could you help me with writing custom function for Text To Column in VBA?

Here is picture of what I have.


E2:E7 = single values
E11 = unseparated string
G11, H11, I11 = result of Text To Column
window in the right = conditional formatting with custom formula "=OR(E2=$G$11:$I$11)" to compare E2:E7 with G11:I11

Thank you very much.

  • I'm a bit confused of your example. Do you have the single and concatenated values in a column? Where the value you are comparing and is it fixed ($E$2) through the range, or it is relative to the position of the range? – Vylix Apr 8 '17 at 3:40
  • We can help you write the VBa... Why don't you share what you've tried so we can help – Dave Apr 8 '17 at 7:14
  • In your example E11 is not a "VALUE", it's a String; it's an array of characters. Character 1 is "0", not Zero. Character 6 is "|" and so on. So stating unseparated values isn't a term in Excel, it's a String in programming language and usually referenced in Strings of data and words like Tokens are used and Delimiter. If you know what String, Token, and Delimiter is, then that's a start. And you didn't disclose if 001.1, or xxx.x is always 3-characters then a period then a single character. This seems like fake data for a larger picture of course. There are strict rules so strict definitions – ejbytes Apr 8 '17 at 22:26
  • To start it appears that "001.1|002.1...|..." might be data that would be imported? If so, use a Delimiter of "|", custom delimiter to separate the values upon importing. Again, seems to be a small set of a larger picture, not all the points are made in your presentation. Need strict definitions of how and what the data you are working with actually is; how dynamic, how static, rigidity of data, etc. Also a HUGE problem working with data is "setting up the data". Raw data is a thing by itself, but to experiment with data you have to set up the data correctly; set up of data is 90% of the task – ejbytes Apr 8 '17 at 22:31
  • my data are: single strings in the column and in the end of this column are some of these string unseparated (delimiter |). ex. E2 = 001.1, E3 = 002.1, E4 = 003.1 etc. and in the end, E11 = 001.1|003.1. and I want to scan E11, and if some of the string of E2:E4 is included in the E11, then I want to highlight it. – Roman Žydyk Apr 9 '17 at 14:31

Select the values in Column E (E2:E7), In the conditional formatting, New Rule, Use a Formula to determine which cells to Format write:
=COUNTIF($G$11:$I$11,"*"&E2&"*")>0 and Format
No need to include E2:E7 in the Formula just select it to include in the Conditional Formatting
The formula will Match if E2 for example is in the string of cells G11:I11 if True it will Format the corresponding cell in E
In case you need to compare to E11 only use:
=COUNTIF($E$11,"*"&E2&"*")>0 and Format
no need to separate E11 (Text to column)
Then create anew rule =E2="" and do not Format
In Manage Rule click on this Rule and click under Stop if True same line of this rule

  • thank you. this works, but is it possible to scan whole string (E11), so I dont have to separate values? (G11:I11) – Roman Žydyk Apr 9 '17 at 14:28
  • Include E11 with G11:I11 like $E$11:$I$11 – yass Apr 9 '17 at 14:33
  • Do you want E11 to be highlighted? – yass Apr 9 '17 at 14:36
  • no, the single strings, that are included in that unseparated string. If I have E2 = 001.1, E3 = 002.1, E4 = 003.1 and E11 = 001.1|003.1 - I want to highlight E2 and E4. – Roman Žydyk Apr 9 '17 at 14:41
  • Can you include E11 with G11:I11 or you have F11 that you don't need to include? – yass Apr 9 '17 at 14:43

If both your single and concatenated values are in a single column, then try this in the conditional formatting:


Where E2 is the range of possible substrings of E11.

Like @yass answer, add a new rule for =E2='', Manage Rules, and checks Stop if True

  • thanks, but this will only highlight exact string, but, I need that it should scan that string, and if there is in that string one of the single record, it should highlight that one. – Roman Žydyk Apr 9 '17 at 14:21
  • In that case, you should change the "string" to E2, and compare it to E11, where E2:E9 is the range of the strings you need to compare with the concatenated string in E11. Let me fix my answer. – Vylix Apr 10 '17 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.