Jak podzielić ciąg oparty na „:” w MS-Excel?

58

Moja kolumna programu Excel jest wypełniona takimi słowami:

1.) ABC:DCF
2.) DCF:FED

Chcę podzielić każde słowo na podstawie „:” i umieścić wynik w sąsiednich kolumnach, tak aby „ABC: DCF” w komórce „A: 1” zamieniło się na „ABC” w komórkę „B: 1” i „DCF” w komórce ” C: 1 ”, a także odpowiadające wartości w każdej kolumnie. Jak to zrobić?

microsoft-excel

— Andrea
źródło

63

Przejdź na kartę Dane, a następnie opcję Tekst na kolumny. Później wybierz opcję „Rozdzielane”, a następnie wybierz „inne” i wstaw dowolny separator.

— BrOSs
źródło

80

Tekst do kolumn będzie działał. Inną opcją, jeśli chcesz zachować oryginalną wartość, jest użycie formuł:
w B1

=left(a1,find(":",a1)-1)

w C1

=mid(a1,find(":",a1)+1,len(a1))

— nutsch
źródło

2

Oryginalną wartość można zachować nawet w innym rozwiązaniu (możesz określić inną kolumnę, aby przechowywać nowe wartości), ale bardziej podoba mi się to rozwiązanie, ponieważ pozwala zawsze mieć aktualne wartości (tj. Jeśli zmodyfikujesz A1, B1 i C1 zaktualizują się, a opcja zamiany tekstu na kolumnę nie).

— psychowood

To genialne rozwiązanie

— jsg

26

Jeśli możesz używać VBA, możesz skorzystać z tej Split()funkcji. Oto funkcja zdefiniowana przez użytkownika (UDF), której można używać w komórce. Dzieli się na wybór postaci i zwraca n- ty element listy podziału.

Zobacz Jak dodać VBA w MS Office? po informacje na temat definiowania UDF.

Function STR_SPLIT(str, sep, n) As String
    Dim V() As String
    V = Split(str, sep)
    STR_SPLIT = V(n - 1)
End Function

Musisz wprowadzić:

=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half

— Jamie Bull
źródło

1

Bardzo miło, nie wiedziałem, że tak łatwo jest tworzyć własne formuły

— zachodzi

1

Jest to idealne rozwiązanie do dzielenia adresu URL na części składowe.

— Cofnij się

7

Wklej go do B1 i wypełnij kolumny po prawej i rzędy w dół:

=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

Edycja: Wcześniej opublikowałem zlokalizowaną wersję formuły, gdzie „,” zostało zastąpione przez „;”. To nie działa w amerykańskiej wersji programu Excel:

=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))

— Hardi Uutma
źródło

1

Witamy w Super User. Czy możesz dodać kilka zdań do swojej odpowiedzi, aby wyjaśnić, co to działa i jak działa? Zwiększy to jego wartość edukacyjną. Dzięki.

— fixer1234

Tak, jasne. Robi to samo, co robi Tekst do kolumn z karty Dane, tyle że z formułą. Możesz zastąpić „:” innym separatorem lub odwołać się do separatora z innej komórki.

— Hardi Uutma

Excel twierdzi, że nie jest to poprawna formuła po wklejeniu do komórki. Sprawdź i zaktualizuj.

— thilina R

Cześć Thilina R! Dziękujemy za powiadomienie. Dokonałem korekty dla amerykańskiej wersji programu Excel. Daj mi znać, jeśli masz teraz z tym jakieś problemy lub coś jest niejasne.

— Hardi Uutma,

Bardzo dobrze. Jedyna jak dotąd odpowiedź, która pozwala radzić sobie z tyloma ogranicznikami, ile tylko chcesz, bez tworzenia własnej funkcji.

— CWilson,