2

Я ищу формулу ячейки, чтобы найти / идентифицировать / проверить, если соседняя ячейка содержит символы в правильном формате.

Если есть символ «|», то перед ним должно следовать любое количество текста, а затем еще один «|» до окончания текста или пробела, иначе ячейка будет неправильной.

Примеры:

|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

Есть идеи?

|источник

3 ответа3

1

Еще один способ постановки проблемы:

  1. || (последовательные |) не допускаются, а также те, которые содержат только два или более пробелов *.
  2. Если существует | | (один пробел между ними) в проверяемом тексте, ему должно предшествовать любое количество не | текст, с | или другой | | непосредственно перед этим, и за ним должно немедленно следовать любое количество не | текст с последующим | или другой | | ,
  3. Если нет | | тогда либо не должно быть | или ровно два | ,

Условие 1. технически явно исключено в вопросе ("любое количество текста" может означать, что нет или допускается только пробел), но из примеров можно сделать вывод, что это является целью ОП.

С измененными условиями, как указано выше, решение, основанное только на формулах, становится легко очевидным, как показано на следующем листе:

Снимок экрана рабочего листа

Это формула, введенная в 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")

Объяснение:

Предварительно подтвержденный вариант формулы выглядит следующим образом:

=
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"
)

Три вышеприведенных условия могут быть изменены следующим образом:

[a] Должно быть еще 2 | чем те, которые приходятся на | | с (первый и последний).

а также

[b] Если таковые существуют | , должно быть как минимум два из них, и первые два из них должны быть разделены хотя бы одним непробельным символом.

Формула для [а]:

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

Формула для внутри- | Текстовая часть проверки [b]:

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

Другая часть [b] (то есть, что не может быть только один |) обрабатывается функцией CHOOSE() , которая также заботится о том случае, когда | (требуется, поскольку этот крайний случай вызывает ошибки в формуле [b] и неверный результат для формулы [a]).

Первый аргумент функции CHOOSE() ,

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

наносит на карту возможные количества | s к индексам 1 , 2 и 3 например так: [0,1,2,3,4,…] → [1,2,3,3,3,…] , и, таким образом, функция возвращает TRUE для счетчика 0 , FALSE для счетчика 1 и результат функции AND() для всех остальных счетчиков.

* Условие не позволяет два или более внутри- | пробелы можно ослабить с помощью функции TRIM() .

|источник
0

Предполагая каждый |xyz| находится в отдельной ячейке, эта формула сделает работу для |xyz| в A1:

=IFERROR(IF(LEFT(A1,1)="|",IF(FIND("|",A1,2)=LEN(A1),"ok","not ok"),"not ok"),"not ok")
|источник
0

Вау, это был сложный вопрос!
Но после 4 часов спотыкаясь в темноте, я думаю, что я получил правильный шаблон RegEx

Я создал небольшую функцию VBA, чтобы дать вам новый тип формулы =RegExTest() .
Формула проверяет, соответствует ли данный шаблон ячейке для проверки, и возвращает истину или ложь.

Сначала я попытался найти все действительные ячейки. Я потерпел неудачу, шаблон был слишком длинным.
Тогда у меня возникла идея: почему бы не искать недопустимые комбинации символов? 

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

Идея позади

Шаблон имеет 4 условия. Если какой-либо из них соответствует недопустимой частичной строке, формула вернет true. В противном случае он возвращает false, что означает, что в проверенной ячейке нет ошибок.

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 подробно

  1. Шаблон типа (xxx | yyy | zzz) является группой с 3 условиями, и одно условие должно быть истинным
  2. Сочетание \| выступает за | символ, который должен быть экранирован, потому что | один особенный персонаж
  3. \w+ обозначает все буквы az или цифры 0-9 или подчеркивания. Поскольку следующая пользовательская функция использует параметр ignorecase = true , вам не нужно указывать заглавные буквы AZ
  4. ^ обозначает начало значения ячейки и $ - окончание значения ячейки
  5. \s обозначает один пробел. Я игнорирую тот факт, что это также означает табуляцию и разрывы строк

Источник: Язык регулярных выражений - краткий справочник по MSDN

Как использовать RegEx в Excel

Это то, что большинство людей не знают. Вы на самом деле можете использовать RegEx в любой программе Office.
Откройте редактор VBA с помощью ALT+F11, добавьте новый модуль и вставьте код

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

Теперь доступен новый тип формулы: =RegExTest( <cell_to_check> , <RegEx_pattern> ) которая будет возвращать true, если шаблон где-либо совпадает с проверенным значением ячейки

В случае, если это кому-то нужно, вот пример рабочей книги

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .