Excelissä on joukko TEKSTI -toimintoja, jotka voivat tehdä ihmeitä. Näillä toiminnoilla voit tehdä kaikenlaisia tekstiviipaleita ja noppaa.
Yksi tekstidatan kanssa työskentelevien ihmisten yleisistä tehtävistä on poimia Excelistä alimerkkijono (eli saada tekstistä psrt solusta).
Valitettavasti Excelissä ei ole alijono -toimintoa, joka voi tehdä tämän helposti. Tämä voidaan kuitenkin tehdä käyttämällä tekstikaavoja sekä joitain muita sisäänrakennettuja Excel-ominaisuuksia.
Katsotaanpa ensin joitain tekstitoimintoja, joita käytämme tässä opetusohjelmassa.
Excel TEXT -toiminnot
Excelissä on useita tekstitoimintoja, joiden avulla alijonon poimiminen Excelin alkuperäisestä tekstistä olisi todella helppoa. Tässä ovat Excel -tekstitoiminnot, joita käytämme tässä opetusohjelmassa:
- OIKEA -toiminto: Poimii määritetyn määrän merkkejä tekstimerkkijonon oikealta puolelta.
- LEFT -toiminto: Poimii määritetyn määrän merkkejä tekstimerkkijonon vasemmalta puolelta.
- MID -toiminto: Poimii määritetyn määrän merkkejä määritetystä aloituskohdasta tekstimerkkijonossa.
- FIND -funktio: Etsii määritetyn tekstin aloituskohdan tekstimerkkijonosta.
- LEN -toiminto: Palauttaa merkkijonon merkkien määrän.
Pura alimerkkijono Excelistä toimintojen avulla
Oletetaan, että sinulla on alla näkyvä tietojoukko:
Nämä ovat satunnaisia (mutta supersankareita) sähköpostitunnuksia (paitsi minun), ja alla olevissa esimerkeissä näytän sinulle, kuinka poimia käyttäjätunnus ja verkkotunnus käyttämällä Excelin tekstitoimintoja.
Esimerkki 1 - Käyttäjänimien poimiminen sähköpostitunnuksista
Tekstitoimintoja käytettäessä on tärkeää tunnistaa kuvio (jos sellainen on). Tämä tekee kaavan rakentamisesta todella helppoa. Yllä olevassa tapauksessa malli on @ -merkki käyttäjänimen ja verkkotunnuksen välillä, ja käytämme sitä viitteenä käyttäjätunnusten saamiseksi.
Tässä on kaava käyttäjätunnuksen saamiseksi:
= VASEN (A2, ETSI ("@", A2) -1)
Yllä oleva kaava käyttää LEFT -funktiota käyttäjänimen purkamiseen tunnistamalla @ -merkin sijainti tunnuksessa. Tämä tehdään käyttämällä FIND -funktiota, joka palauttaa @: n sijainnin.
Esimerkiksi kun kyseessä on [email protected], FIND (“@”, A2) palauttaa 11, joka on sen sijainti tekstimerkkijonossa.
Nyt käytämme VASEN -funktiota poimiaksesi 10 merkkiä merkkijonon vasemmalta puolelta (yksi vähemmän kuin LEFT -funktion palauttama arvo).
Esimerkki 2 - Verkkotunnuksen purkaminen sähköpostitunnuksista
Edellä olevassa esimerkissä käytettyä samaa logiikkaa voidaan käyttää toimialueen nimen saamiseen. Pieni ero tässä on se, että meidän on purettava merkit tekstimerkkijonon oikealta puolelta.
Tässä on kaava, joka tekee tämän:
= OIKEA (A2, LEN (A2) -FIND ("@", A2))
Yllä olevassa kaavassa käytämme samaa logiikkaa, mutta säädä sitä varmistaaksemme, että saamme oikean merkkijonon.
Otetaan jälleen esimerkki [email protected]. FIND -funktio palauttaa @ -merkin sijainnin, joka on tässä tapauksessa 11. Nyt meidän on purettava kaikki merkit @-merkin jälkeen. Joten tunnistamme merkkijonon kokonaispituuden ja vähennämme merkkien määrän @-merkkiin asti. Se antaa meille merkkien määrän, jotka kattavat oikealla olevan verkkotunnuksen.
Nyt voimme yksinkertaisesti käyttää OIKEA -funktiota verkkotunnuksen saamiseksi.
Esimerkki 3 - Verkkotunnuksen purkaminen sähköpostitunnuksista (ilman .com)
Jos haluat poimia alimerkkijonon tekstimerkkijonon keskeltä, sinun on tunnistettava merkin sijainti juuri ennen ja jälkeen alimerkkijonon.
Esimerkiksi alla olevassa esimerkissä, jos haluat saada verkkotunnuksen nimen ilman .com -osaa, merkki olisi @ (joka on juuri ennen verkkotunnuksen nimeä) ja. (joka on heti sen jälkeen).
Tässä on kaava, joka purkaa vain verkkotunnuksen nimen:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Excel MID -toiminto poimii määritetyn määrän merkkejä määritetystä aloituskohdasta. Tässä esimerkissä FIND (“@”, A2) +1 määrittää lähtökohdan (joka on heti@-merkin jälkeen) ja FIND (“.”, A2) -FIND (“@”, A2) -1 tunnistaa merkkien lukumäärän@' ja '.‘
Päivittää: Yksi lukijoista William19 mainitsi, että yllä oleva kaava ei toimi, jos sähköpostitunnuksessa on piste (.) (Esimerkiksi [email protected]). Joten tässä on kaava tällaisten tapausten käsittelyyn:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Tekstin käyttäminen sarakkeisiin alimerkkien poimimiseksi Excelissä
Funktioiden käyttäminen Excel -alijonon poimimiseen on se etu, että se on dynaaminen. Jos muutat alkuperäistä tekstiä, kaava päivittää tulokset automaattisesti.
Jos et ehkä tarvitse tätä, Teksti sarakkeisiin -ominaisuuden käyttäminen voisi olla nopea ja helppo tapa jakaa teksti alijonoiksi määritettyjen merkkien perusteella.
Voit tehdä tämän seuraavasti:
- Valitse solut, joissa on teksti.
- Valitse Data -> Datatyökalut -> Teksti sarakkeisiin.
- Valitse ohjatun tekstin sarakkeeseen -vaiheessa vaiheessa 1 Eroteltu ja paina Seuraava.
- Valitse vaiheessa 2 Muu -vaihtoehto ja kirjoita @ sen oikeanpuoleiseen ruutuun. Tämä on erottimemme, jota Excel käyttäisi jakamaan tekstin alijonoiksi. Näet tietojen esikatselun alla. Napsauta Seuraava.
- Vaiheessa 3 Yleiset asetukset toimivat tässä tapauksessa hyvin. Voit kuitenkin valita toisen muodon, jos jaat numeroita/päivämääriä. Oletuksena kohdesolu on paikka, jossa sinulla on alkuperäiset tiedot. Jos haluat säilyttää alkuperäiset tiedot ennallaan, vaihda tämä johonkin toiseen soluun.
- Napsauta Valmis.
Tämä antaa sinulle heti kaksi alimerkkijoukkoa kutakin tässä esimerkissä käytettyä sähköpostitunnusta varten.
Jos haluat jakaa tekstin edelleen (esimerkiksi jakaa batman.com batmaniksi ja comiksi), toista sama prosessi sen kanssa.
FIND- ja REPLACE -toimintojen käyttäminen tekstin poimimiseksi Excelin solusta
FIND ja REPLACE voivat olla tehokas tekniikka, kun työskentelet tekstin kanssa Excelissä. Alla olevissa esimerkeissä opit käyttämään FIND- ja REPLACE -toimintoja yleismerkkien kanssa hämmästyttävien asioiden tekemiseen Excelissä.
Katso myös: Opi kaikki Excelin yleismerkeistä.
Otetaan samat sähköpostitunnusten esimerkit.
Esimerkki 1 - Käyttäjänimien purkaminen sähköpostitunnuksista
Voit poimia käyttäjätunnukset sähköpostitunnuksista Find and Replace -toiminnon avulla seuraavasti:
- Kopioi ja liitä alkuperäiset tiedot. Koska Etsi ja korvaa -toiminto toimii ja muuttaa tietoja, joihin sitä sovelletaan, on parasta ottaa varmuuskopio alkuperäisistä tiedoista.
- Valitse tiedot ja siirry Koti -> Muokkaus -> Etsi ja valitse -> Korvaa (tai käytä pikanäppäintä Ctrl + H).
- Kirjoita Etsi ja korvaa -valintaikkunaan seuraava:
- Löytää mitä: @*
- Korvaa seuraavalla: (jätä tämä tyhjäksi)
- Napsauta Korvaa kaikki.
Tämä poistaa välittömästi sähköpostin tunnuksissa @ -merkin edellisen tekstin. Saat seuraavan kuvan:
Miten tämä toimii ?? - Yllä olevassa esimerkissä olemme käyttäneet yhdistelmää @ ja *. Tähti (*) on jokerimerkki, joka edustaa mitä tahansa määrää merkkejä. Siksi @* tarkoittaisi tekstimerkkijonoa, joka alkaa @ -merkillä ja jonka jälkeen voi olla mikä tahansa määrä merkkejä. Esimerkiksi osoitteessa [email protected] @* olisi @batman.com. Kun @* korvataan tyhjällä, se poistaa kaikki merkit @: n jälkeen (mukaan lukien @).
Esimerkki 2 - Verkkotunnuksen purkaminen sähköpostitunnuksista
Käyttämällä samaa logiikkaa voit muokata Etsi -ehtoja saadaksesi verkkotunnuksen nimen.
Tässä on vaiheet:
- Valitse tiedot.
- Siirry etusivulle -> Muokkaus -> Etsi ja valitse -> Korvaa (tai käytä pikanäppäintä Ctrl + H).
- Kirjoita Etsi ja korvaa -valintaikkunaan seuraava:
- Löytää mitä: *@
- Korvaa seuraavalla: (jätä tämä tyhjäksi)
- Napsauta Korvaa kaikki.
Tämä poistaa välittömästi sähköpostin tunnuksissa @ -merkin edellisen tekstin. Saat seuraavan kuvan:
