Kā sadalīt ciparus un tekstu no virknes programmā Excel

Anonim

Daudzas reizes es saņemu dažādus datus no lauka un servera analīzei. Šie dati parasti ir netīri, un kolonna ir sajaukta ar ciparu un tekstu. Veicot datu tīrīšanu pirms analīzes, es atdalu ciparus un tekstu atsevišķās kolonnās. Šajā rakstā es jums pastāstīšu, kā to izdarīt.

Scenārijs:
Tātad viens mūsu draugs vietnē Exceltip.com uzdeva šo jautājumu komentāru sadaļā. “Kā atdalīt ciparus, kas atrodas pirms teksta un teksta beigās, izmantojot Excel formulu. Piemēram, 125EvenueStreet un LoveYou3000 utt. ”

Lai iegūtu tekstu, mēs izmantojam RIGHT, LEFT, MID un citas teksta funkcijas. Mums vienkārši jāzina izvilkamo tekstu skaits. Un šeit mēs vispirms darīsim to pašu.
Izņemiet skaitli un tekstu no virknes, ja numurs ir virknes beigās
Iepriekš minētajam piemēram es esmu sagatavojis šo lapu. Šūnā A2 man ir virkne. Šūnā B2 es vēlos teksta daļu, bet C2 - numura daļu.

Tāpēc mums vienkārši jāzina pozīcija, no kuras sākas skaitlis. Tad mēs izmantosim kreiso un citu funkciju. Tātad, lai iegūtu pirmā numura pozīciju, mēs izmantojam zemāk esošo vispārīgo formulu:
Vispārīgā formula, lai iegūtu pirmā numura pozīciju virknē:

= MIN (MEKLĒT ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Tas atgriezīs pirmā numura pozīciju.
Iepriekš minētajā piemērā uzrakstiet šo formulu jebkurā šūnā.

= MIN (MEKLĒT ({0,1,2,3,4,5,6,7,8,9}, A5 un "0123456789"))

Izvilkt teksta daļu

Tas atgriezīs 15, jo pirmais atrastais skaitlis ir 15. vietā tekstā. Es to paskaidrošu vēlāk.

Tagad, lai iegūtu tekstu, no kreisās mums vienkārši jāiegūst 15-1 rakstzīme no virknes. Tātad mēs izmantosim
Funkcija LEFT teksta izvilkšanai.
Formula teksta izvilkšanai no kreisās puses

= LEFT (A5, MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)


Šeit mēs vienkārši atņēmām 1 no jebkāda MIN atgriezta skaitļa (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 un "0123456789")).
Izvilkt numura daļu

Tagad, lai iegūtu skaitļus, mums vienkārši jāiegūst skaitļu rakstzīmes no atrastā pirmā numura. Tātad mēs aprēķinām kopējo garumu virkne un atņem pirmā atrastā numura pozīciju un pievieno 1 uz to. Vienkārši. Jā, tas ir vienkārši sarežģīti, tas ir vienkārši.
Formula ciparu izvilkšanai no labās puses

= PAREIZI (A5, LEN (A5) -MIN (MEKLĒT ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))+1)

Šeit mēs tikko ieguvām kopējo virknes garumu, izmantojot funkciju LEN, un pēc tam atņemam pirmā atrastā numura pozīciju un pēc tam pievienojām 1. Tas dod mums kopējo skaitļu skaitu. Uzziniet vairāk šeit par teksta iegūšanu, izmantojot Excel kreiso un labo funkciju.

Tātad LEFT un RIGHT funkciju daļa ir vienkārša. Sarežģītā daļa ir MIN un SEARCH Part, kas dod mums pirmā atrastā numura pozīciju. Sapratīsim to.
Kā tas strādā
Mēs zinām, kā darbojas LEFT un RIGHT funkcija. Mēs izpētīsim šīs formulas galveno daļu, kas iegūst atrastā pirmā numura pozīciju un tas ir: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Funkcija MEKLĒ atgriež teksta pozīciju virknē. Funkcijai MEKLĒT (“teksts”, “virkne”) ir divi argumenti: vispirms teksts, kuru vēlaties meklēt, otrkārt, virkne, kurā vēlaties meklēt.

    • Šeit, SEARCH, teksta pozīcijā mums ir skaitļu masīvs no 0 līdz 9. Un virknes pozīcijā mums ir virkne, kas ir savienota ar "0123456789", izmantojot & operators. Kāpēc? ES tev pateikšu.
    • Katrs masīva {0,1,2,3,4,5,6,7,8,9} elements tiks meklēts noteiktā virknē un atgriezīs savu pozīciju masīva formā virknē tajā pašā indeksa masīvā.
    • Ja kāda vērtība netiek atrasta, tā radīs kļūdu. Tādējādi visas formulas radīs kļūdu. Lai no tā izvairītos, mēs tekstā savienojām ciparus "0123456789". Lai tā vienmēr atrastu katru skaitli virknē. Šie skaitļi galu galā ir, tāpēc neradīs nekādas problēmas.
    • Funkcija MIN atgriež mazāko vērtību no masīva, ko atgriež funkcija MEKLĒT. Šī mazākā vērtība būs pirmais skaitlis virknē. Tagad, izmantojot šo funkciju NUMBER un LEFT and RIGHT, mēs varam sadalīt teksta un virknes daļas.

Izpētīsim mūsu piemēru. A5 mums ir virkne ar ielas nosaukumu un mājas numuru. Mums tie ir jāatdala dažādās šūnās.
Vispirms redzēsim, kā mēs ieguvām pirmā numura pozīciju virknē.

    • MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): tas tiks tulkots MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Tagad, kā es paskaidroju, meklēšana meklēs katru skaitli masīvā {0,1,2,3,4,5,6,7,8,9} collas Monta270123456789 un atgriezīs savu pozīciju masīva formā. Atgrieztais masīvs būs {8,9,6,11,12,13,14,7,16,17}. Kā?
0 tiks meklēts virknē. Tas ir atrodams 8 pozīcijā. Tādējādi mūsu pirmais elements ir 8. Ņemiet vērā, ka mūsu oriģinālais teksts ir tikai 7 rakstzīmes garš. Dabūt to. 0 nav daļa no Monta27.
Nākamais 1 tiks meklēts virknē, un tas arī nav sākotnējās virknes daļa, un mēs iegūstam tā 9. pozīciju.
Nākamie 2 tiks meklēti. Tā kā tā ir sākotnējās virknes daļa, tās indeksu iegūstam kā 6.
Līdzīgi katrs elements ir atrodams kādā vietā.

    • Tagad šis masīvs ir nodots MIN funkcijai kā MIN ({8,9,6,11,12,13,14,7,16,17}). MIN atgriež 6, kas ir pirmā teksta atrašanās vieta sākotnējā tekstā.
      Un stāsts pēc tam ir pavisam vienkāršs. Mēs izmantojam šo skaitļu ekstrakta tekstu un ciparus, izmantojot funkciju LEFT un RIGHT.

Izņemiet skaitli un tekstu no virknes, kad numurs ir virknes sākumā
Iepriekš minētajā piemērā skaitlis bija virknes beigās. Kā iegūt skaitli un tekstu, ja skaitlis ir sākumā.

Esmu sagatavojis līdzīgu tabulu kā iepriekš. Sākumā tam ir tikai skaitlis.

Šeit mēs izmantosim citu tehniku. Mēs saskaitīsim skaitļu garumu (kas šeit ir 2) un iegūsim šo rakstzīmju skaitu no virknes kreisās puses.
Tātad metode ir = LEFT (virkne, skaitļu skaits)
Lai saskaitītu rakstzīmju skaitu, šī ir formula.
Vispārīgā formula skaitļu skaitīšanai:

= SUM (LEN (string) -LEN (SUBSTITUTE (string, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Šeit,

      • Funkcija SUBSTITUTE aizstās katru atrasto numuru ar “” (tukšs). Ja skaitlis tiek atrasts aizstāts un masīvam tiks pievienota jauna virkne, masīvam tiks pievienota cita gudra oriģināla virkne. Tādā veidā mums būs 10 virkņu masīvs.
      • Tagad funkcija LEN atgriezīs rakstzīmju garumu šo virkņu masīvā.
      • Tad no sākotnējo virkņu garuma mēs atņemsim katras virknes garumu, ko atgriež funkcija SUBSTITUTE. Tas atkal atgriezīs masīvu.
      • Tagad SUM pievienos visus šos skaitļus. Tas ir skaitļu skaits virknē.

Izņemiet skaitļa daļu no virknes

Tagad, kopš mēs zinām ciparu garumu virkne, mēs šo funkciju aizstāsim pa kreisi.
Tā kā mums ir mūsu virkne A11 mūsu:

Formula skaitļu izvilkšanai no kreisās puses

= LEFT (A11, SUM (LEN (A11)) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))


Izvilkt teksta daļu no virknes

Tā kā mēs zinām ciparu skaitu, mēs varam to atņemt no virknes kopējā garuma, lai virknē iegūtu ciparu alfabētus, un pēc tam izmantot pareizo funkciju, lai iegūtu šo rakstzīmju skaitu no virknes labās puses.

Formula teksta izvilkšanai no RIGHT

= PAREIZI (A11, LEN (A2) -SUM (LEN (A11) -LEN (AIZSTĀJIS (A11, {"0", "1", "2", "3", "4", "5", "6) "," 7 "," 8 "," 9 "}," "))))


Kā tas strādā
Abās formulās galvenā daļa ir SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))), kas aprēķina pirmo skaitļa parādīšanos. Tikai pēc tam, kad esam to atraduši, mēs varam sadalīt tekstu un ciparus, izmantojot LEFT funkciju. Tāpēc sapratīsim šo.

      • Aizstājējs (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Šī daļa atgriež virknes masīvu A11 pēc šo skaitļu aizstāšanas ar neko/tukšu (“”). Priekš 27Monta tas atgriezīsies {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
      • LEN (AIZSTĀJIS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Tagad SUBSTITUTE daļu iesaiņo LEN funkcija. Šo masīvā esošo tekstu atgriešanas garumu atgriež funkcija SUBSTITUTE. Rezultātā mums būs {7,7,6,7,7,7,7,6,7,7}.
      • LEN (A11) -LEN (AIZSTĀJIS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9" "}," ")): Šeit mēs atņemam katru skaitli, ko atdeva augšējā daļa, no faktiskās virknes garuma. Oriģinālā teksta garums ir 7. Tādējādi mums būs {7-7,7-7,7-6,….}. Visbeidzot, mums būs {0,0,1,0,0,0,0,0,1,0,0}.
      • SUM (LEN (A11) -LEN (AIZSTĀJIS (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Šeit mēs izmantojām SUM, lai summētu masīvu, ko atdeva iepriekšējā funkcijas daļa. Tas dos 2. Kāds ir skaitļu skaits virknē.

Tagad, izmantojot šo, mēs varam iegūt tekstus un numuru un sadalīt tos dažādās šūnās. Šī metode darbosies gan ar teksta veidu, kad skaitlis ir sākumā, gan beigās. Jums tikai pareizi jāizmanto LEFT un RIGHT funkcija.
Izmantojiet funkciju SplitNumText, lai sadalītu ciparus un tekstus no virknes
Iepriekš minētās metodes ir nedaudz sarežģītas, un tās nav noderīgas, ja tiek sajaukts teksts un cipari. Lai sadalītu tekstu un ciparus, izmantojiet šo lietotāja definēto funkciju.

Sintakse:

= SplitNumText (virkne, op)

Virkne: Stīga, kuru vēlaties sadalīt.
Op: tas ir Būla. Iet 0 vai nepatiesa lai iegūtu teksta daļu. Skaitļa daļai iziet taisnība vai jebkurš skaitlis lielāks par 0.
Piemēram, ja virkne ir A20,
Formula skaitļu iegūšanai no virknes ir šāda:

= SplitNumText (A20,1)

Un
Teksta izvilkšanas no virknes formula ir šāda:

= SplitNumText (A20,0)

Nokopējiet zemāk esošo kodu VBA modulī, lai iepriekš minētā formula darbotos.

Funkcija SplitNumText (str kā virkne, op kā Būla) num = "" txt = "" I = 1 līdz Len (str) If IsNumeric (Mid (str, i, 1)) Tad num = num & Mid (str, i , 1) Cits txt = txt & Mid (str, i, 1) Beigt, ja nākamais i Ja op = True, tad SplitNumText = num Cits SplitNumText = txt Beigas, ja beigu funkcija

Šis kods vienkārši pārbauda katru rakstzīmi virknē, vai tas ir skaitlis vai nē. Ja tas ir skaitlis, tad tas tiek saglabāts mainīgajā num citādi txt mainīgajā. Ja lietotājs op izvēlas opciju true, tad tiek atgriezts numurs, citādi tiek atgriezts txt.

Manuprāt, tas ir labākais veids, kā sadalīt skaitļus un tekstu no virknes.
Ja vēlaties, varat lejupielādēt darbgrāmatu šeit.

Tātad, jā, puiši, tie ir veidi, kā sadalīt tekstu un ciparus dažādās šūnās. Paziņojiet man, ja jums ir šaubas vai kāds labāks risinājums, komentāru sadaļā zemāk. Vienmēr ir jautri sazināties ar puišiem.

Noklikšķiniet uz zemāk esošās saites, lai lejupielādētu darba failu:

Sadaliet numuru un tekstu no šūnas

Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti
Funkcija VLOOKUP programmā Excel
COUNTIF programmā Excel 2016
Kā lietot SUMIF funkciju programmā Excel