W tym artykule dowiemy się, jak łączyć wartości tekstowe i sformatowane w programie Excel.
Scenariusz:
Excel traktuje wartości w wielu formatach, takich jak liczba, data, godzina, tekst, procent lub waluta. Można je zamieniać w siebie. Program Excel konwertuje liczby na tekst, aby używać liczb jako tekstu w formułach, takich jak podgląd z liczbami i tekstem. Funkcja TEKST programu Excel pozwala przekonwertować liczbę na tekst. Funkcja TEKST w programie Excel służy do konwertowania liczb na tekst. Zabawne jest to, że możesz sformatować tę liczbę, aby wyświetlić ją w żądanym formacie. Na przykład sformatuj datę rr-mm-dd na format dd-mm-rr. Dodaj znaki waluty przed liczbą i wiele innych.
Formuła TEKST w Excelu
Funkcja TEKST to funkcja tekstowa, która konwertuje dowolną wartość na podany format. Wynik może wydawać się, że jest to liczba, ale jest w formacie tekstowym.
| =TEKST(odniesienie_komórki, format_tekstu) |
cell_ref : wartość do przekonwertowania przy użyciu odwołania do komórki
Text_format : Format do konwersji
| Format | Format wyjściowy |
| D | 7 |
| ddd | czw |
| dddd | czwartek |
| m | 2 |
| mmm | luty |
| mmmm | Luty |
| mmmmm | F |
| yy | 19 |
| rrrr | 2019 |
| m/d/rok | 2/7/19 |
| mm/dd/rr | 02/07/19 |
| mm/dd/rrrr | 02/07/2019 |
| ddd, mmm d | Czw, 7 lutego |
| mm/dd/rrrr h:mm AM/PM | 02.07.2019 14:15 |
| mm/dd/rrrr gg:mm:ss | 07/02/2019 14:15:00 |
Przykład :
Wszystko to może być trudne do zrozumienia. Rozumiem, jak korzystać z funkcji na przykładzie. Oto kilka przykładów konwersji wartości dat na format tekstowy lub inny wymagany format.
Konwertuj wartość w komórce A2.
Użyj wzoru w komórce C2
| =TEKST (A2, „0”) |
„0”: zwraca ogólny format tekstu.
Naciśnij Enter i skopiuj formułę w pozostałych komórkach za pomocą Ctrl + D
Jak widać, otrzymaliśmy liczby jako tekst wyjściowy, ponieważ liczba istotności zawsze się zmieniała.
Tutaj mamy kilka liczb do przekonwertowania na format tekstowy lub inny wymagany format.
Użyj wzoru:
| =TEKST(A2;"d") |
Jak widać, wartość w komórce wyjściowej jest w formacie tekstowym.
Możesz użyć dowolnego Format_text i wykonywać swoją pracę w programie Excel bez żadnych przerw
Czasami konieczne jest dodanie 0 przed określoną cyfrą numeru, taką jak numer telefonu lub numer PIN. Użyj tej formuły tekstu, aby to zrobić…
| =TEKST( 1234, “00000”) |
Jeśli masz N cyfr liczby, to jako argument w formacie tekstowym wpisz n+1 0s.
Napisz tę formułę tekstową, aby dodać walutę.
| =TEKST( 1234, “$0”) |
Jak widać, otrzymaliśmy liczby jako tekst wyjściowy, ponieważ liczba istotności zawsze się zmieniała.
Kod VBA do konwersji waluty w formacie TEKSTOWYM
Ten kod jest podzielony na cztery indywidualne funkcje. Główną funkcją jest NUM_TO_IND_RUPEE_WORD. Pozostałe trzy funkcje GetHunderds(), GetTens() i GetDigits pomagają funkcji, która pomaga funkcji głównej w tworzeniu ciągu.
Kod
| Funkcja NUM_TO_IND_RUPEE_WORD (ByVal MyNumber, opcjonalne incRupees As Boolean = True)
Dim Crores, Lakhs, Rupie, Paise, Temp Dim DecimalMiejsce tak długie, licz tak długo Dim myLakhs, myCrores ReDim Place(9) Jako ciąg Miejsce (2) = " Tysiąc " : Miejsce (3) = " Milion " Miejsce(4) = „Miliard”: Miejsce(5) = „Bilion”
' Łańcuchowa reprezentacja kwoty. MójNumer = Przytnij(Str(MójNumer))
' Pozycja miejsca dziesiętnego 0, jeśli brak. DecimalPlace = InStr(MyNumber, ".")
' Konwertuj Paise i ustaw MyNumber na kwotę w rupiach. Jeśli DziesiętneMiejsce > 0 Wtedy Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left (MyNumber, DecimalPlace - 1)) Zakończ, jeśli
mojeKrory = Mój Numer \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MójNumer = MójNumer - myCrores * 10000000 - myLakhs * 100000 Liczba = 1
Rób, gdy myCrores „” Temp = GetHundreds(Right(myCrores, 3)) Jeśli Temp „” To Crores = Temp i miejsce (liczba) i Crores Jeśli Len(myCrores) > 3 Wtedy myCrores = Left(myCrores, Len(myCrores) - 3) W przeciwnym razie mojeKrory = "" Zakończ, jeśli Liczenie = Liczenie + 1 Pętla Liczba = 1
Rób, gdy myLakhs „” Temp = GetHundreds(Right(myLakhs, 3)) Jeśli Temp „” Wtedy Lakhs = Temp i Miejsce (liczba) i Lakhs Jeśli Len(myLakhs) > 3 Wtedy myLakhs = Left(myLakhs, Len(myLakhs) - 3) W przeciwnym razie myLakhs = "" Zakończ, jeśli Liczenie = Liczenie + 1 Pętla Liczba = 1
Zrób, gdy mój numer „” Temp = GetHundreds (Right (MyNumber, 3)) Jeśli Temp „” Wtedy Rupie = Temp i Miejsce (Liczba) i Rupie Jeśli dł.(mój numer) > 3 wtedy MójNumer = Lewy(MójNumer, Len(MójNumer) - 3) W przeciwnym razie MójNumer = "" Zakończ, jeśli Liczenie = Liczenie + 1 Pętla
Wybierz przypadek Crores Wielkość liter „”: Crores = „” Przypadek „jeden”: crores = „jeden crore” Przypadek inny: crores = crores i " crores " Koniec Wybierz Wybierz Case Lakhs Przypadek „”: Lakhs = „” Przypadek „Jeden”: Lakhs = „Jeden Lakh” Przypadek inny: Lakhs = Lakhs i " Lakhs " Koniec Wybierz Wybierz przypadek rupii Wielkość liter "": Rupie = "Zero" Przypadek „Jeden”: Rupie = „Jeden” Przypadek inny: Rupie = Rupie Koniec Wybierz Wybierz przypadek Paise Wielkość liter "": Paise = " i Tylko Paise Zero " Przypadek „Jeden”: Paise = „ i Paise Tylko jeden ” Case Else: Paise = " i Paise " & Paise i " Tylko " Koniec Wybierz 'tworzenie ciągu słów do przetłumaczenia liczby na słowa NUM_TO_IND_RUPEE_WORD = IIf(incRupees, "Rupees", "") & Crores & _ Lakhs & Rupees & Paise Koniec funkcji ' Konwertuje liczbę od 100 do 999 na tekst Funkcja GetHundreds(ByVal MyNumber) Wynik przyciemnienia jako ciąg Jeśli Val(MyNumber) = 0, to zakończ funkcję MójNumer = Prawo("000" & MójNumer, 3) Przekształć setki miejsc. Jeśli Mid(Mój Numer, 1, 1) „0” Wtedy Wynik = GetDigit(Mid(MyNumber, 1, 1)) & "Sto" Zakończ, jeśli ' Zamień miejsca dziesiątek i jedynek. Jeśli Mid(Mój Numer, 2, 1) „0” Wtedy Wynik = Wynik i GetTens(Mid(MyNumber, 2)) W przeciwnym razie Wynik = wynik i pobierz cyfrę (środek (mój numer, 3)) Zakończ, jeśli GetHundreds = Wynik Koniec funkcji ' Konwertuje liczbę od 10 do 99 na tekst. Funkcja GetTens(TensText) Wynik przyciemnienia jako ciąg Wynik = "" ' Unieważnia tymczasową wartość funkcji. If Val(Left(TensText, 1)) = 1 Then ' Jeśli wartość z zakresu 10-19… Wybierz wielkość liter (TensText) Przypadek 10: Wynik = „Dziesięć” Przypadek 11: Wynik = „Jedenaście” Przypadek 12: Wynik = „Dwanaście” Przypadek 13: Wynik = „Trzynaście” Przypadek 14: Wynik = „Czternaście” Przypadek 15: Wynik = „Piętnaście” Przypadek 16: Wynik = „szesnaście” Przypadek 17: Wynik = „siedemnaście” Przypadek 18: Wynik = „Osiemnaście” Przypadek 19: Wynik = „Dziewiętnaście” Przypadek inny Koniec Wybierz Else ' Jeśli wartość między 20-99… Wybierz wielkość liter (lewo (TensText, 1)) Przypadek 2: Wynik = „Dwadzieścia” Przypadek 3: Wynik = „Trzydzieści” Przypadek 4: Wynik = „Czterdzieści” Przypadek 5: Wynik = „Pięćdziesiąt” Przypadek 6: Wynik = „Sześćdziesiąt” Przypadek 7: Wynik = „Siedemdziesiąt” Przypadek 8: Wynik = „Osiemdziesiąt” Przypadek 9: Wynik = „Dziewięćdziesiąt” Przypadek inny Koniec Wybierz Wynik = wynik i pobierz cyfrę _ (Right(TensText, 1)) ' Pobierz jedno miejsce. Zakończ, jeśli GetTens = Wynik Koniec funkcji ' Konwertuje liczbę od 1 do 9 na tekst. Funkcja GetDigit(cyfra) Wybierz wielkość liter (cyfra) Przypadek 1: GetDigit = „Jeden” Przypadek 2: GetDigit = „Dwa” Przypadek 3: GetDigit = „Trzy” Przypadek 4: GetDigit = „Cztery” Przypadek 5: GetDigit = „Pięć” Przypadek 6: GetDigit = „Sześć” Przypadek 7: GetDigit = „Siedem” Przypadek 8: GetDigit = „Osiem” Przypadek 9: GetDigit = „Dziewięć” Inny przypadek: GetDigit = "" Koniec Wybierz Koniec funkcji |
Powyższy kod otrzyma wynik w poprawnym formacie. Teraz stwórz własną, dostosowaną funkcję.
Łączenie ciągu tekstowego i wartości obliczonej przez formułę. Aby wynik zwracany przez jakąś formułę był bardziej zrozumiały dla użytkowników, możesz połączyć go z ciągiem tekstowym wyjaśniającym, jaka w rzeczywistości jest wartość.
Na przykład możesz użyć następującej formuły, aby zwrócić bieżącą datę:
| =CONCATENATE("Dzisiaj jest", TEKST(DZIŚ(); "dd-mmm-rr")) |
Korzystanie z funkcji CONCATENATE w programie Excel - rzeczy do zapamiętania
Aby mieć pewność, że formuły CONCATENATE zawsze dostarczają prawidłowe wyniki, pamiętaj o następujących prostych zasadach:
Funkcja CONCATENATE programu Excel wymaga do działania co najmniej jednego argumentu „text”.
Oto wszystkie notatki obserwacyjne przy użyciu wzoru w Excelu
Uwagi:
- Użyj operatora & do łączenia tekstu. & operator działa tak samo jak funkcja CONCAT
- W nowych wersjach programu Excel funkcja ZŁĄCZ.TEKSTY została zastąpiona funkcją ZŁĄCZ.TEKST, która ma dokładnie taką samą składnię. Funkcja ZŁĄCZ.TEKSTY jest zachowywana w celu zapewnienia zgodności z poprzednimi wersjami. Powszechną praktyką jest używanie funkcji ZŁĄCZ.TEKSTY, ponieważ program Excel nie daje żadnych obietnic, że funkcja ZŁĄCZ.TEKSTY będzie dostępna w przyszłych wersjach programu Excel.
Mam nadzieję, że ten artykuł na temat łączenia wartości tekstowych i sformatowanych w programie Excel jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Excel REPLACE vs funkcja SUBSTITUTE: Funkcje REPLACE i SUBSTITUTE są najbardziej niezrozumianymi funkcjami. Aby znaleźć i zamienić dany tekst, używamy funkcji SUBSTITUTE. Gdzie REPLACE służy do zastępowania liczby znaków w ciągu.
Jak korzystać z funkcji CZY.TEKST w programie Excel? : zwraca wartość logiczną PRAWDA, jeśli wartość komórki jest tekstem przy użyciu funkcji CZY.TEKST w programie Excel.
Jak wyróżnić komórki zawierające określony tekst w programie Excel : Zaznacz komórki na podstawie formuły, aby znaleźć określoną wartość tekstową w komórce w programie Excel.
Konwertuje sekundy dziesiętne na format czasu : Jak wiemy czas w Excelu jest traktowany jako liczby. Godziny, minuty i sekundy są traktowane jako liczby dziesiętne. Więc kiedy mamy sekundy jako liczby, jak przekonwertować na format czasu? Ten artykuł został omówiony.
Oblicz minuty między datami i godzinami w programie Excel : obliczenie różnicy czasu jest dość łatwe. Wystarczy odjąć czas rozpoczęcia od czasu zakończenia. Dowiedz się więcej o tej formule, klikając link
Zastąp tekst na końcu ciągu, zaczynając od pozycji zmiennej: Aby zastąpić tekst z końca ciągu, używamy funkcji REPLACE. Funkcja REPLACE używa pozycji tekstu w ciągu do zamiany.
Popularne artykuły :
50 skrótów Excela, które zwiększą Twoją produktywność : Szybsze wykonywanie zadań w programie Excel. Te skróty pomogą Ci zwiększyć wydajność pracy w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.