2

I am looking for a cell formula to find/identify/validate if an adjacent cell contains characters in the correct format.

If there is a character "|", then it must be preceded or followed by any amount of text and then another "|" before the text ends or there is a space, otherwise the cell is wrong.

Examples:

|Name| |Surname| |City| = ok
|Name| |Surname| = ok
|Name| |Surname| New York = ok
New York |Name| |Surname| = ok
|Name| |City |Surname| = wrong
N|ame |City| |Surname| = wrong
|Surname| |ZipCode| = ok

Any ideas?

  • 1
    All that data is in a single cell? Can you tell us what you've already tried to make this work? – CharlieRB Nov 5 '13 at 18:43
1

Another way of stating the problem is:

  1. || (consecutive |) are not allowed, nor those enclosing only two of more spaces*.
  2. If there exists a | | (one space in between) in the text to be validated, it must immediately be preceded by any amount of non-| text, with a | or another | | immediately prior to that, and it must immediately be followed by any amount of non-| text followed by a | or another | |.
  3. If there are no | | then there must either be no | or exactly two |.

Condition 1. is, technically, explicitly ruled out in the question, ("any amount of text" can mean none or space-only is allowed) but it can be inferred from the examples that this is the intent of the OP.

With the conditions re-worded as above a formula-only solution becomes readily apparent as seen applied in the following worksheet:

Screenshot of worksheet

This is the formula entered into B2:B11:

=IF(CHOOSE(MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))),TRUE,FALSE,AND(LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2,LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0)),"ok","wrong")

Explanation:

The prettified version of the formula is as follows:

=
IF(
  CHOOSE(
    MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))),
    TRUE,
    FALSE,
    AND(
      LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2,
      LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0
    )
  ),
  "ok",
  "wrong"
)

The three conditions above can be refactored to the following:

[a] There must be precisely 2 more | than those accounted for by the | |s (the first and the last ones).

and

[b] If there exist any |, there must be at least two of them, and the first two of them must be separated by at least one non-space character.

The formula for [a] is:

LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2

The formula for the intra-| text validation part of [b] is:

LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0

The other part of [b] (i.e., that there can't only be one |) is taken care of by the CHOOSE() function, which also takes care of the case when there are no | (required since this edge case causes errors in formula [b] and an incorrect result for formula [a]).

The first argument of the CHOOSE() function,

MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|","")))

maps the possible counts of |s to the indexes 1, 2, and 3 like so: [0,1,2,3,4,…][1,2,3,3,3,…], and thus the function returns TRUE for a count of 0, FALSE for a count of 1, and the result of the AND() function for all other counts.


* The condition not allowing two or more intra-| spaces can be relaxed by the use of the TRIM() function.

| improve this answer | |
0

Assuming each |xyz| is in an individual cell, this formula will do the job for the |xyz| in A1:

=IFERROR(IF(LEFT(A1,1)="|",IF(FIND("|",A1,2)=LEN(A1),"ok","not ok"),"not ok"),"not ok")
| improve this answer | |
0

Wow, that was a tough question!
But after 4 hours stumbling in the dark, I think I got the right RegEx pattern

I created a small VBA function to give you a new type of formula =RegExTest().
The formula checks if a given pattern matches the cell to check and returns true or false.

First, I tried to search all valid cells. I failed, the pattern was way too long.
Then I got the idea: Why not searching the invalid character combinations?

(\w+\|\w+|(^|\s)\w+\||\|\w+($|\s)|\|\|)

enter image description here

The idea behind

The pattern has 4 conditions. If any of them matches an invalid partial string, the formula will return true. Otherwise it returns false which means there are no errors in the checked cell

CONDITION#1    or    CONDITION#2   or   CONDITION#3    or    CONDITION#4 
\w+ \| \w+          (^|\s) \w+ \|       \| \w+ ($|\s)          \| \|
"abc|abc"         "^abc|" or " abc|"   "|abc " or "|abc$"       "||"

RegEx in detail

  1. A pattern like (xxx|yyy|zzz) is a group with 3 conditions and one condition must be true
  2. The combination \| stands for the | character which must be escaped because | alone is a special character
  3. \w+ stands for all letters a-z or numbers 0-9 or underlines. Since the following user-defined function uses the ignorecase = true setting, you don't need to specify uppercase letters A-Z
  4. ^ stands for the cell value beginning and $ for the cell value ending
  5. \s stands for a single space. I ignore the fact that it also stands for tab and line breaks

Source: Regular Expression Language - Quick Reference on MSDN

How to use RegEx in Excel

That is what most people don't know. You actually can use RegEx in any Office program.
Open the VBA editor with ALT+F11, add a new module and paste the code

Function RegExTest(rngCell As Range, strPattern As String) As Boolean        
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBScript.RegExp")        
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    objRegEx.Pattern = strPattern        
    RegExTest = objRegEx.Test(rngCell.Value)        
End Function

Now, a new type of formula is available: =RegExTest( <cell_to_check> , <RegEx_pattern> ) which will return true if the pattern matches anywhere onto the checked cell value

In case anyone needs it, here is an example workbook

| improve this answer | |

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.