Jak sprawić, aby program Excel ignorował znaki specjalne i spacje podczas pobierania danych z innej komórki?

0

Mam skoroszyt programu Excel, który służy do tworzenia ofert dla klientów. Automatycznie generuje unikalny numer oferty na podstawie nazwy sprzedawcy, nazwy firmy otrzymującej ofertę, daty i numeru wydania (jeśli ten sam sprzedawca sporządza dwie osobne oferty dla tej samej firmy, dla tej samej firmy dzień, pierwszy będzie miał numer wydania 01, drugi będzie miał numer wydania 02 itd.)

Typowy numer oferty wypełniony informacjami pozorowanymi będzie wyglądał następująco:

JS-ABC-05052016-01

„JS” to inicjały sprzedawcy (John Smith.) „ABC” to pierwsze trzy znaki nazwy firmy (ABCompany.) „05052016” to dzisiejsza data, a „01” to numer wydania. To wszystkie informacje wprowadzone przez sprzedawcę.

Formuła używana w komórce, która generuje i wyświetla numer oferty, to:

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

Powyższa formuła pobiera fragmenty informacji wprowadzanych do komórek i kompiluje je w celu wygenerowania numeru oferty.

Występuje jednak problem, gdy pierwsze trzy znaki nazwy firmy zawierają spację lub znak specjalny. Na przykład nazwa firmy „AB Company” generuje następujący numer oferty:

JS-A. -05052016-01

Innym przykładem jest to, że nazwa firmy „A&B Company” wygenerowałaby następujący numer oferty:

JS-A i B-05052016-01

W dalszej części procesu ofertowego nazwa skoroszytu zostanie zmieniona, aby zawierała numer oferty. Może to powodować problemy, w których numer oferty zawiera znak specjalny, taki jak kropka (na przykład może zepsuć typ pliku).

Czy istnieje sposób, aby Excel ignorował jakiekolwiek znaki (w tym spacje) w nazwie firmy, które nie są literami lub cyframi? Na przykład utworzenie firmy o nazwie „A. & B. Firma ”generuje numer oferty:

JS-ABC-05052016-01

microsoft-excel worksheet-function vba

— Adam W.
źródło

więc musisz zmienić to, co pobiera formuła, a nie tylko zastąpić znaki z już pobranych danych? Może to być praca dla wyrażenia regularnego?

— Raystafarian

0

Myślę, że znalazłem odpowiedź dla ciebie

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 - Wstaw - Moduł - Wklej.

Jeśli chcesz, możesz dodać więcej znaków specjalnych do tablicy. tutaj:sSpecialChars = "\/:*?""<>|$&"

Następnie w swojej komórce wpisz: =SUBSTITUTE(removeSpecial(A1)," ","")

wynikiem a &b.companyjestab.company

Podziękowania dla autora, właśnie go znalazłem.

wtedy twoja formuła powinna być:

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

— mirusev
źródło

Nie jest to jednak oznaczone VBA

— Raystafarian

To rozwiązanie działało dla mnie idealnie. Przepraszam - nie pomyślałem o dołączeniu tagu VBA, chociaż mój arkusz roboczy mocno używa VBA, więc dodanie nowej funkcji nie stanowiło problemu. Zmodyfikowałem pytanie, dodając tag VBA i oznaczono jako rozwiązany!

— Adam W,

0

Niby ból, ale zastąp =LEFT(D11,3)w swojej funkcji

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

Więc jest to

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

Spowoduje to wyszukanie ., usunięcie i &- dodanie innych, jeśli istnieją.

— Raystafarian
źródło