Preskoči na glavno vsebino

Kako ločiti besedilo in številke iz ene celice v dva stolpca?

Če imate stolpec besedilnih nizov, ki so sestavljeni iz besedila in številk, bi radi besedilo in številke iz ene celice ločili v dve različni celici, kot je prikazano na sliki spodaj. V Excelu lahko to nalogo zaključite s temi metodami.


1. način: besedilo in številke ločite s formulami v Excelu

Z naslednjimi formulami lahko besedilo in številke iz celice izvlečete v dve ločeni celici. Naredite naslednje:

1. Vnesite to formulo v prazno celico - C3, kamor želite umestiti rezultat: =LEVO(A3,MIN(NAJDI(0,1,2,3,4,5,6,7,8,9 {},A3&"0123456789"))-1)(A3 je celica, ki vsebuje besedilni niz, ki ga želite ločiti), in pritisnite Vnesite tipko, da dobite samo besedilo iz celice A2. Oglejte si posnetek zaslona:

doc razdeljeno besedilo številka 2

2. Nato lahko izvlečete številke iz celice z uporabo te formule: =DESNO(A3,LEN(A3)-MIN(NAJDI(0,1,2,3,4,5,6,7,8,9 {},A3&"0123456789"))+1),( A3 je celica, ki vsebuje besedilni niz, ki ga želite ločiti), vnesite to formulo v celico D3, v katero želite postaviti številke, in pritisnite Vnesite tipka, potem boste dobili številke, kot sledi:

doc razdeljeno besedilo številka 3

3. Nato izberite celico C3: D3 in povlecite ročico za polnjenje do celic, v katere želite vsebovati te formule, in vidite, da so besedilo in številke ločeni v različne celice:

doc razdeljeno besedilo številka 4


Razdelite ali ločite besedilne nize v posamezne besedilne in številske stolpce:

Kutools za Excel's Razdeljene celice funkcija je močno orodje, lahko vam pomaga razdeliti vrednosti celic na več stolpcev ali vrstic, pomaga pa vam tudi pri razrezovanju alfanumeričnih nizov v ločene stolpce besedila in števil itd. Kliknite za prenos Kutools za Excel!

doc razdeljeno besedilo številka 16

2. način: Ločite besedilo in številke s programom Flash Fill v programu Excel 2013 in novejši različici

Z zgornjimi formulami lahko ločite besedilo in številke, če je besedilo pred številkami. Če želite ločiti besedilni niz, ki je pred besedilom, lahko uporabite Flash Fill funkcija Excel 2013 in novejše različice.

Če imate Excel 2013 in novejšo različico, se Flash Fill funkcija vam lahko pomaga pri izpolnjevanju besedila v enem stolpcu in številk v drugem stolpcu, naredite naslednje:

1. Številke svojega prvega besedilnega niza vnesite popolnoma v sosednjo prazno celico - B3, glejte posnetek zaslona:

doc razdeljeno besedilo številka 5

2. Nato izberite obseg B3: B7, kamor želite vnesti številke, in kliknite datum > Flash Fillin v celice so bile naenkrat vnesene samo številke, glejte posnetek zaslona:

doc razdeljeno besedilo številka 6

3. Nato besedilni niz popolnoma vnesite v celico C3, glejte posnetek zaslona:

doc razdeljeno besedilo številka 7

4. In izberite obseg celic C3: C7, kamor želite vnesti samo besedilo, kliknite datum > Flash Fill kot tudi korak 2, in vidite, da je bilo besedilo ločeno na naslednji način:

doc razdeljeno besedilo številka 8

Nasvet: Zapolnjeni ročaj lahko povlečete tudi v obseg, ki ga želite uporabiti, in nato kliknete Možnost samodejnega polnjenja in preveri Flash Fill.

doc razdeljeno besedilo številka 9

3. način: Ločite besedilo in številke, ki se nepravilno mešajo z uporabniško določeno funkcijo

Če imate besedilni niz, ki vsebuje nepravilno mešano besedilo in številke, kot so prikazani naslednji podatki, Excel ne podpira splošne funkcije za reševanje te težave, lahko pa ustvarite uporabniško določeno funkcijo, da to dokončate.

doc razdeljeno besedilo številka 17

1. Držite tipko ALT + F11 tipke za odpiranje Okno Microsoft Visual Basic for Applications.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.

Koda VBA: ločite besedilo in številke v različne celice iz ene celice

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Nato shranite in zaprite to kodo, se vrnite na delovni list, vnesite to formulo = SplitText (A3, FALSE) v prazno celico, da dobite edini besedilni niz, nato pa povlecite ročico za polnjenje navzdol do celic, ki jih želite izpolniti s to formulo, glejte posnetek zaslona:

doc razdeljeno besedilo številka 10

4. In thne, vnesite formulo = SplitText (A3, TRUE) v drugo celico in povlecite ročico za polnjenje navzdol do celic, v katere želite izpolniti to formulo, da dobite številke, glejte posnetek zaslona:

doc razdeljeno besedilo številka 11

Opombe: Rezultat bo napačen, če so v besedilnem nizu decimalna števila.


4. način: Besedilo in številke ločite v dva stolpca s programom Kutools za Excel

Če imate Kutools za Excel, s svojim močnim orodjem Razdeljene celice Pripomoček lahko besedilne nize hitro razdelite na dva stolpca: eden je številka, drugi pa besedilo.

Po namestitvi Kutools za Excel, naredite naslednje:

1. Izberite obseg podatkov, v katerem želite ločiti besedilo in številko.

2. Nato kliknite Kutools > Spoji in razdeli > Razdeljene celice, glej posnetek zaslona:

3. v Razdeljene celice pogovorno okno, izberite Razdeli na stolpce možnost pod tip in nato preverite Besedilo in številka Iz Razdeljeno po razdelek, glej posnetek zaslona:

doc razdeljeno besedilo številka 13

4. Nato kliknite Ok in pojavilo se bo pozivno polje, ki vas bo opozorilo, da izberete celico za izpis rezultata, glejte posnetek zaslona:

doc razdeljeno besedilo številka 14

5. Kliknite OK , besedilni nizi v izboru pa so ločeni v dva stolpca, kot je prikazano na sliki spodaj:

doc razdeljeno besedilo številka 15

Kliknite Prenesi Kutools za Excel in brezplačno preskusno različico zdaj!


Besedilo in številke ločite s programom Kutools za Excel

Kutools za Excel: z več kot 300 priročnimi dodatki za Excel, brezplačno preizkusite brez omejitev v 30 dneh. Prenesite in brezplačno preskusite zdaj!

Sorodni članki:

Kako razdeliti besedo na ločene črke v Excelu?

Kako razdeliti celice v več stolpcev ali vrstic z vrnitvijo kočije?


 

  • Kutools za Excel: 300+ priročnih funkcij za Excel brezplačnega preskusa

    Zbere več kot 300 pametnih in profesionalnih orodij za Excel, tako da v nekaj kliki pri vsakodnevnem delu poenostavi različne vrste zapletenih nalog, kar lahko prihrani veliko časa in poveča produktivnost.

    • Več delovnih listov ali delovnih zvezkov združite v en sam delovni zvezek ali delovni list
    • Štetje in seštevanje celic glede na ozadje, pisavo ali barvo pogojnega oblikovanja
    • Pošljite isto e-pošto posameznemu seznamu prejemnikov z različnimi prilogami
    • 110,000+ Excel uporabnikov. 30-dnevna brezplačna preizkusna različica, kreditna kartica ni potrebna!
    • 60-dnevna garancija vračila denarja!
    kutools 1 kutools plus
  • Več delovnih listov ali delovnih zvezkov združite v en sam list ali delovni zvezek

    Pri vsakodnevnem delu lahko združevanje več delovnih listov ali delovnih zvezkov v en list ali delovni zvezek predstavlja ogromno delo, če jih želite kombinirati enega za drugim. Ampak, s Kutools za Excel's Združite funkcijo, lahko to nalogo rešite z le nekaj kliki.

    kombinirajte liste
  • Štetje in seštevanje celic glede na ozadje, pisavo ali barvo pogojnega oblikovanja

    Številčne ali vsote vrednosti celic glede na ozadje, pisavo ali barvo pogojnega oblikovanja v velikem obsegu je lahko težka naloga, Kutools za Excel podpira enostavno orodje-Štej po barvah kar vam lahko pomaga, da se s tem poslom čim hitreje spoprimete brez kakršnih koli spretnosti v Excelu.

    štetje po barvi
  • Pošljite isto e-pošto posameznemu seznamu prejemnikov z različnimi prilogami

    Aplikacijo Word lahko uporabite za pošiljanje osebnih e-poštnih sporočil več osebam brez priponk, vendar z Kutools za Excel's Pošlji e-pošto funkcijo, je to omejitev mogoče odpraviti. Hkrati lahko sporočila CC ali Bcc pošljete tudi določeni osebi.

    pošlji e-pošto

 

Comments (34)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Tenho uma coluna com vários endereços e os números de endereços. Conforme tabela abaixo. Como separo em uma coluna apenas os nomes e outra coluna apenas os números?

endereço
Avenida Angélica 1235
Rua José Maria Lisboa 456
Rua Celso de Azevedo Marques 307
Rua Rio Duas Barras 953
This comment was minimized by the moderator on the site
Hello, Mateus,
To extract the address number only from the address, the folloiwng formula may help you:
=SUMPRODUCT(MID(0&B3, LARGE(INDEX(ISNUMBER(--MID(B3, ROW(INDIRECT("1:"&LEN(B3))), 1)) * ROW(INDIRECT("1:"&LEN(B3))), 0), ROW(INDIRECT("1:"&LEN(B3))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B3)))/10)

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Bom dia.
Consegui extrair o número, muito obrigado!
Mas como eu consigo extrair agora apenas o endereço?
This comment was minimized by the moderator on the site
Hello, Mateus,
If you want to extract both address and address number, you can apply the Method 3 in this article:

https://www.extendoffice.com/documents/excel/2701-excel-separate-text-and-numbers.html#a3

After copying and pasting the code, please apply the below formulas:
Extract address: =SplitText(B2,FALSE)

Extract address number: =SplitText(B2,TRUE)
Please try, hope it can help you! If you have any other problem, please comment here.
This comment was minimized by the moderator on the site
Agile (11/20/2017 12:00:00 AM)How can I separate this cell into just "Agile" and "(11/20/2017 12:00:00 AM)"
This comment was minimized by the moderator on the site
Hello, Milner,To get the name text, please use this formula: =TRIM(LEFT(A1, FIND("(", A1)-1))To get the date time, please apply this formula: =MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("(",A1)+1)Please try the formulas, hope them can help you!
This comment was minimized by the moderator on the site
0002786961 TRAK CDFA #: 0008787942 2722 2723 4536841 N/A 4345784 001018809~00077480

Above is an example of data line I need to split these into 3 types: First: starts with 2 and is of 4 digit (2722 in above example) Second: starts with 2 and is of 7 digit third; starts with 4 is of 7 digit.
I tried separating everything into different columns and then putting if and conditions that I mentioned above but the problem is not everything is getting separated and splitting everything is not efficient enough. I am not able to figure out a vba code for something that satisfies all the conditions and works too.
Can anyone help me out?
This comment was minimized by the moderator on the site
Hello, menze
Do you need to split this three parts 2722 2723 4536841 from the long data? If so, you just can use the Text to Column feature in Excel to split the content into multiple cells by space, then delete the extra data and only keep the data you need.
If not, please upload your Excel file or screenshot here for a reference.
Thank you!
This comment was minimized by the moderator on the site
I have a requirement code that I need to separate out from the text, looks like this 3.1.1.2. Line-of-sight Range Requirements (T=O) The vehicle and units shall be capable of line-of-sight connectivity to all nodes or within a 10-mile radius, whichever is greater. In one column I need the code and the other I need the text. Can you help?
This comment was minimized by the moderator on the site
I have address like 12,anna street,98413256789 i want to seperate address and contact number(mobile or landline) two columns. Is it possible.Pls help to solve
This comment was minimized by the moderator on the site
Hi, Mohan,To seperate address and contact number, please apply the below formulas:Address: =LEFT(A1,LEN(A1)-12)Mobile number: =RIGHT(A1,11)
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Please, help me with the VBA code required to take out "INC000010542805" out of "User KSmith Audit ID INC000010542805 Comment None Control Data".
I have 1,000 rows of this type of data with different number of characters but always with that "INC" string.
This comment was minimized by the moderator on the site
Hello, Kenny,
May be the below formula can solve your problem, please try:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("INC",A1),LEN(A1))," ",REPT(" ",100)),100))
This comment was minimized by the moderator on the site
skyyang thanks very much, it works. I really appreciate your help
This comment was minimized by the moderator on the site
how to separate number and letters (1122AB). I tried the upper formula but its not working with me. anybody help me in this regard. Thanks in advance
This comment was minimized by the moderator on the site
Hello, Naeem,
The above formula only works if the text is before the numbers, your numbers before the text, so i recommend the second and third method for you!
Please try, hope it can help you!
This comment was minimized by the moderator on the site
kereeeennn... berhasil....
This comment was minimized by the moderator on the site
Thanks worked for Alpha-numeric cell data [ =RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)]
This comment was minimized by the moderator on the site
Hello Can anyone help me? I want to remove this zero and braceket by an excel formula.
1 BR PE-0.50CT(H SI2)- -0.00( )-2 SP PE-0.50CT(H SI1)-1 RU PE-0.40CT(H-SI)-750GF-RG-RING-25-40-2.50GM


Gowtam
This comment was minimized by the moderator on the site
VERY USEFUL FORMULA I LIKE IT.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations