Как объединить несколько вложенных замещающих функций в Excel?

Я пытаюсь настроить функцию для форматирования строки, которая позже будет конкатенирована. Пример строки будет выглядеть так:

Standard_H2_W1_Launch_123x456_S_40K_AB

Хотя иногда "S" не существует, а иногда "40K" - "60K" или нет, а "_AB" также может быть "_CD" или "EF". Наконец, все подчеркивания должны быть заменены на дефисы. Конечный продукт должен выглядеть следующим образом:

Standard-H2-W1-Launch-123x456-

У меня есть четыре функции, которые, если они выполнялись один за другим, позаботятся обо всем этом:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_")

=SUBSTITUTE(SUBSTITUTE(B2,"_40K",""),"_60K","")

=SUBSTITUTE(C2,"_S_","_")

=SUBSTITUTE(D2,"_","-")

Я пробовал несколько способов объединить их в одну функцию, но я относительно новичок в этом уровне excel, поэтому я в недоумении. Есть ли способ объединить все это, чтобы он выполнял одну команду за другой в одной ячейке?

Ответ 1

Чтобы просто объединить их, вы можете разместить их вместе следующим образом:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_"),"_40K",""),"_60K",""),"_S_","_"),"_","-")

(обратите внимание, что это может передать более старый предел Excel из 7 вложенных операторов. Я тестирую в Excel 2010

Другой способ сделать это - использовать функции Left и Right.

Это предполагает, что изменяющиеся данные на конце всегда присутствуют и имеют длину 8 символов

=SUBSTITUTE(LEFT(A2,LEN(A2)-8),"_","-")

Это приведет к той же результирующей строке

Если строка не всегда заканчивается 8 символами, которые вы хотите удалить, вы можете найти "_S" и получить текущее местоположение. Попробуйте следующее:

=SUBSTITUTE(LEFT(A2,FIND("_S",A2,1)),"_","-")

Ответ 2

Я бы использовал следующий подход:

=SUBSTITUTE(LEFT(A2,LEN(A2)-X),"_","-")

где X обозначает длину вещей, которые вы не после. И для X я бы использовал

(ISERROR(FIND("_S",A2,1))*2)+
(ISERROR(FIND("_40K",A2,1))*4)+
(ISERROR(FIND("_60K",A2,1))*4)+
(ISERROR(FIND("_AB",A2,1))*3)+
(ISERROR(FIND("_CD",A2,1))*3)+
(ISERROR(FIND("_EF",A2,1))*3)

Приведенный выше ISERROR(FIND("X",.,.))*x будет возвращать 0, если X не найден и X (длина X), если он найден. Так что технически вы обрезаете A2 справа с возможными совпадениями.

Преимущество такого подхода над другим, упомянутым выше, заключается в том, что более очевидно, что происходит замена (или удаление), поскольку "подстановка" не является вложенной.

Ответ 3

Спасибо за идею разрушения формулы Вернера!

Использование Alt + Enter позволяет поместить каждый бит сложной формулы-заменителя на отдельные строки: им становится легче следовать и автоматически выстраиваться в линию при нажатии Enter.

Просто убедитесь, что у вас достаточно конечных операторов, чтобы соответствовать количеству строк substitute( с обеих сторон ссылки на ячейку.

Как в этом примере:

=
substitute(
substitute(
substitute(
substitute(
B11
,"(","")
,")","")
,"[","")
,"]","")

становится:

=
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(B12,"(",""),")",""),"[",""),"]","")

который отлично работает как есть, но всегда можно удалить лишние абзацы вручную:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B12,"(",""),")",""),"[",""),"]","")

Имя > substitute()

[Американское Самоа] > Американское Самоа