Как заставить Excel игнорировать специальные символы и пробелы при получении данных из другой ячейки?

Question

У меня есть книга Excel, которая используется для составления предложений для клиентов. Он автоматически генерирует уникальный номер предложения, основанный на имени продавца, названии компании, получающей предложение, дате и номере выпуска (если два отдельных предложения составляются одним и тем же продавцом для одной и той же компании. первый выпуск будет иметь номер 01, второй будет выпуск 02 и т. д.)

Наполненный фиктивной информацией, типичный номер предложения будет выглядеть так:

JS-ABC-05052016-01

«JS» - инициалы продавца (Джон Смит). «ABC» - это первые три символа названия компании (ABCompany.) «05052016» - это сегодняшняя дата, а «01» - номер выпуска. Это вся информация, введенная продавцом.

Формула, используемая в ячейке, которая генерирует и отображает номер предложения:

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(D11,3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

Приведенная выше формула берет части информации, которые вводятся в ячейки, и компилирует их для генерации номера предложения.

Однако существует проблема, когда первые три символа названия компании содержат пробел или специальный символ. Например, название компании «AB Company» будет генерировать следующий номер предложения:

JS-A. -05052016-01

Другим примером является то, что название компании «A & B Company» будет генерировать следующий номер предложения:

JS-А & Б-05052016-01

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

Есть ли способ заставить Excel игнорировать любые символы (включая пробелы) в названии компании, которые не являются буквами или цифрами? Например, создание компании под названием «А. & B. Company 'генерирует номер предложения:

JS-ABC-05052016-01

Answer 1

Вид боли, но замените =LEFT(D11,3) в вашей функции на

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D11,".","")," ",""),".",""),"&",""),3)

Так что это

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D11,".","")," ",""),".",""),"&",""),3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

Что ищет . , пусто и & - добавить других, если они существуют.

Answer 2

Я думаю, что нашел ответ для вас

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|$&"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

Alt+F11 - Вставить - Модуль - Вставить.

Вы можете добавить больше специальных символов в массив, если хотите. здесь: sSpecialChars = "\/:*?""<>|$&"

Затем в своей ячейке введите: =SUBSTITUTE(removeSpecial(A1)," ","")

Результатом a &b.company является ab.company

Кредиты автору, я только что нашел его.

тогда ваша формула должна быть:

=UPPER(LEFT(SUBSTITUTE(removeSpecial(P4)," ",""),1)&LEFT(SUBSTITUTE(removeSpecial(P5)," ",""),1)&"-"&LEFT(SUBSTITUTE(removeSpecial(D11)," ",""),3)&"-"&LEFT(SUBSTITUTE(removeSpecial(K6)," ",""),2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),4,2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),7,4)&"-"&SUBSTITUTE(removeSpecial(D5)," ",""))