Как я могу объединить несколько вложенных функций замены в 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, поэтому я в недоумении. Можно ли как-то объединить все это так, чтобы оно выполняло одну команду за другой в одной ячейке?


excel nested excel-formula substitution nested-function
person samanthathyme    schedule 11.03.2014    source источник

Ответы (5)


arrow_upward
27
arrow_downward

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

=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)),"_","-")
person Automate This    schedule 11.03.2014
comment
Большое спасибо! Я попробовал ваш первый вариант, прежде чем опубликовать свой вопрос. Должно быть, я сделал ошибку, так как не мог заставить это решение работать. После использования вашей строки я смог использовать комбинированную функцию. Спасибо! - person samanthathyme; 11.03.2014

arrow_upward
2
arrow_downward

Спасибо за идею разбить формулу Вернера!

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

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

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

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

становится:

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

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

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

Имя > заменить()

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

person Phil    schedule 25.10.2016

arrow_upward
2
arrow_downward

  • вложение SUBSTITUTE() в строку может быть неприятным, однако его всегда можно упорядочить:

Панель формул скриншота

person player0    schedule 11.05.2018

arrow_upward
1
arrow_downward

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

=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 справа с возможными совпадениями.

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

person Werner    schedule 11.03.2014

arrow_upward
-1
arrow_downward 

=SUBSTITUTE(text, old_text, new_text)

if: a=!, b=@, c=#,... x=>, y=?, z=~, " "="     "
then: abcdefghijklmnopqrstuvwxyz ... try this out
equals: !@#$%^&*()-=+[]\{}|;:/<>?~     ...     ;}?     ;*(|     ]:;

ПРАВИЛА:

(1) текст для замены находится в ячейке A1
(2) не более 64 уровней замещения (в приведенной ниже формуле всего 27 уровней [алфавит + пробел])
(2) "old_text" также не может быть "new_text" (т.е. если a=z .: z не может быть "старым текстом")

---so if a=z,b=y,...y=b,z=a, then the result is 
---abcdefghijklmnopqrstuvwxyz = zyxwvutsrqponnopqrstuvwxyz (and z changes to a then changes back to z) ... (pattern starts to fail after m=n, n=m... and n becomes n)

Формула:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a","!"),"b","@"),"c","#"),"d","$"),"e","%"),"f","^"),"g","&"),"h","*"),"i","("),"j",")"),"k","-"),"l","="),"m","+"),"n","["),"o","]"),"p","\"),"q","{"),"r","}"),"s","|"),"t",";"),"u",":"),"v","/"),"w","<"),"x",">"),"y","?"),"z","~")," ","     ")
person user12380371    schedule 15.11.2019
comment
Ответы, которые представляют собой только код без контекста или объяснения, обычно считаются некачественными. Пожалуйста, рассмотрите возможность добавления дополнительной информации к вашему ответу, чтобы улучшить его. - person brae; 15.11.2019