Вопрос или проблема
Существует ли способ сделать так, чтобы Foo, FOO и fOO превращались в Bar, BAR и bAR, не вводя все возможные перестановки в функцию по отдельности?
=SUBSTITUTE(A1,"foo","bar") чувствителен к регистру, SUBSTITUTE(lower(A1),"foo", "bar") подходит для некоторых приложений, но удаляет любые существующие различия в регистре из A1; так что FoofooFOO превратится в barbarbar. Очевидно, что вы можете использовать несколько замен, чтобы поймать все вхождения, но иногда это действительно затруднительно.
Большое спасибо.
Ужасный, но функциональный ответ.
Работает для любых 2 строк одинаковой длины.
Просто высказываю свое мнение, надеясь, что кто-то сможет предложить лучшее решение (возможно, массивная формула?)
Требуются вспомогательные столбцы. Верхний ряд вспомогательных столбцов использовался для Индекса.
Формулы:
A2 : (оригинальная строка)
C1 : пустая, или 0 (необходимо для следующего столбца)
C2:C... : =SUBSTITUTE(UPPER(A2),"FOO","BAR")
D1 : =B1+1
D2:D... : =IF(EXACT(MID($A2,C$1,1),UPPER(MID($A2,C$1,1))),MID($B2,C$1,1),LOWER(MID($B2,C$1,1)))
B2 : конкатенировать (или textjoin, если вы используете новый excel)
Протяните столбец D, пока не получите достаточное количество столбцов. Можно оценить визуально или использовать =max(len(A:A)), чтобы найти максимальную длину.
Теперь, когда Excel поддерживает функции Лямбда, не-VBA ответ:
SUBSTITUTE_I = LAMBDA(Text, OldText, NewText, [InstanceNum],
IFS(
Text = "", "",
OldText = "", Text,
TRUE, LET(
_oldLen, LEN(OldText),
_maxInstance, INT(LEN(Text) / _oldLen),
_pos, SCAN(0, SEQUENCE(1, _maxInstance),
LAMBDA(_acc, _curr,
IF(_acc = "", "", IFERROR(SEARCH(OldText, Text, _acc + 1), ""))
)
),
_count, COUNT(_pos),
_instance, N(InstanceNum),
IFS(
_count = 0, Text,
ABS(_instance) > _count, Text,
_instance > 0, REPLACE(Text, INDEX(_pos, 1, _instance), _oldLen, NewText),
_instance < 0, REPLACE(Text, INDEX(_pos, 1, _count + _instance + 1), _oldLen, NewText),
TRUE, LET(
_lenDiff, LEN(NewText) - _oldLen,
_nPos, TAKE(_pos, 1, _count) + SEQUENCE(1, _count, 0, _lenDiff),
REDUCE(Text, _nPos,
LAMBDA(_acc, _curr,
REPLACE(_acc, _curr, _oldLen, NewText)
)
)
)
)
)
)
);
Ответ или решение
Замена строк с учетом регистра в Excel: Подход без VBA
В данной статье рассматривается реализация функции замены строк в Excel, которая поддерживает регистронезависимую замену, сохраняя при этом отличия в регистре оригинальной строки. Пример, который рассмотрим, состоит в том, чтобы заменить подстроку "Foo", "FOO" и "fOO" на "Bar", "BAR" и "bAR" соответственно, без необходимости вручную перечислять все возможные варианты регистров.
Основные ограничения стандартных функций Excel
Стандартная функция SUBSTITUTE в Excel не может решить эту задачу, так как она чувствительна к регистру. Применение SUBSTITUTE(LOWER(A1), "foo", "bar") позволяет заменить без учета регистра, но приводит к потере оригинального формата строки. Следовательно, требуется более гибкий подход.
Решение с использованием функций Excel
Одним из простых, но функциональных решений является создание пользовательской функции с использованием возможности LAMBDA, которая поддерживается в новых версиях Excel.
SUBSTITUTE_I = LAMBDA(Text, OldText, NewText, [InstanceNum],
IFS(
Text = "", "",
OldText = "", Text,
TRUE, LET(
_oldLen, LEN(OldText),
_maxInstance, INT(LEN(Text) / _oldLen),
_pos, SCAN(0, SEQUENCE(1, _maxInstance),
LAMBDA(_acc, _curr,
IF(_acc = "", "", IFERROR(SEARCH(OldText, Text, _acc + 1), ""))
)
),
_count, COUNT(_pos),
_instance, N(InstanceNum),
IFS(
_count = 0, Text,
ABS(_instance) > _count, Text,
_instance > 0, REPLACE(Text, INDEX(_pos, 1, _instance), _oldLen, NewText),
_instance < 0, REPLACE(Text, INDEX(_pos, 1, _count + _instance + 1), _oldLen, NewText),
TRUE, LET(
_lenDiff, LEN(NewText) - _oldLen,
_nPos, TAKE(_pos, 1, _count) + SEQUENCE(1, _count, 0, _lenDiff),
REDUCE(Text, _nPos,
LAMBDA(_acc, _curr,
REPLACE(_acc, _curr, _oldLen, NewText)
)
)
)
)
)
)
);Пояснение работы функции
-
Логика работы: Основная идея функции заключается в том, чтобы сначала определить позиции всех вхождений искомой подстроки
OldTextв строкеText. Затем, используя эти позиции, производится замена с учетом регистровости. -
Параметры функции:
Text: Исходная строка, в которой будет выполнена замена.OldText: Подстрока, которую нужно заменить.NewText: Подстрока, на которую нужно выполнить замену.[InstanceNum]: Номер экземпляра для замены (необязательный параметр).
-
Преимущества: Использование функции LAMBDA позволяет создавать более универсальные и адаптивные формулы, которые могут быть легко изменены и улучшены по мере необходимости.
Дополнительные рекомендации
- Тестирование: После создания функции рекомендуется проверить несколько вариантов строк и убедиться, что результат соответствует ожидаемому. Это включает в себя строки с различными комбинациями регистра.
- Экспорт и использование: Функция может быть сохранена в качестве имени вашего файла Excel, что позволяет легко использовать ее в других книгах.
- Производительность: Будьте осторожны при использовании в больших таблицах, так как функции, использующие цепочки ссылок, могут замедлить работу Excel.
Заключение
Инновационная функция, основанная на LAMBDA, обеспечивает удобный и функциональный инструмент для случаев, когда необходимо производить регистронезависимые замены с сохранением оригинального формата текста. Используйте это решение для автоматизации и оптимизации ваших задач в Excel.