Etsi merkin viimeisen esiintymän sijainti Excelissä

Tässä opetusohjelmassa opit löytämään merkin viimeisen esiintymän sijainnin merkkijonossa Excelissä.

Muutama päivä sitten eräs kollega keksi tämän ongelman.

Hänellä oli luettelo URL -osoitteista, kuten alla on esitetty, ja hänen täytyi poimia kaikki merkit viimeisen kauttaviivan (“/”) jälkeen.

Esimerkiksi osoitteesta https://example.com/archive/tammikuu hänen täytyi poimia 'tammikuu'.

Olisi ollut todella helppoa, jos URL -osoitteissa olisi vain yksi vinoviiva.

Hänellä oli valtava tuhansien luettelo eri pituisista URL-osoitteista ja vaihteleva määrä eteenpäin suuntautuvia viiltoja.

Tällaisissa tapauksissa temppu on löytää URL -osoitteen edellisen viivan viimeisen esiintymän sijainti.

Tässä opetusohjelmassa näytän sinulle kaksi tapaa tehdä tämä:

• Excel -kaavan käyttäminen
• Mukautetun toiminnon käyttäminen (luotu VBA: n kautta)

Hahmon viimeisen sijainnin saaminen Excel -kaavan avulla

Kun sinulla on viimeisen tapahtuman sijainti, voit yksinkertaisesti poimia mitä tahansa sen oikealta puolelta OIKEALLA toiminnolla.

Tässä on kaava, joka löytää vinoviivan viimeisen sijainnin ja purkaa kaikki tekstit sen oikealta puolelta.

= OIKEA (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", ""))), 1 )) 

Kuinka tämä kaava toimii?

Hajotetaan kaava ja selitetään, miten sen jokainen osa toimii.

• KORVA (A2, ”/”,“”) - Tämä kaavan osa korvaa kauttaviivan tyhjällä merkkijonolla. Jos esimerkiksi haluat löytää jonkun muun merkkijonon kuin vinoviivan, käytä sitä tässä.
• LEN (A2) -LEN (VAIHTOEHTO (A2, ”/”),“”)) - Tässä osassa kerrotaan, kuinka monta merkkiviivassa on vinoviivaa. Se yksinkertaisesti vähentää merkkijonon pituuden ilman kauttaviivaa merkkijonon pituudesta eteenpäin.
• VAIHTOEHTO (A2, ”/”, ”@”, LEN (A2) -LEN (VAIHTOEHTO (A2, ”/”, ””))) - Tämä kaavan osa korvaa viimeisen kauttaviivan @-merkillä. Ajatuksena on tehdä hahmosta ainutlaatuinen. Voit käyttää mitä tahansa haluamaasi merkkiä. Varmista vain, että se on ainutlaatuinen eikä näy jo merkkijonossa.
• ETSI (“@”, KORVAA (A2, ”/”, ”@”, LEN (A2) -LEN (VAIHTOEHTO (A2, ”/”, ””))), 1) - Tämä kaavan osa antaa sinulle viimeisen vinoviivan sijainnin.
• LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””)))), 1) - Tämä kaavan osa kertoo meille, kuinka monta merkkiä on viimeisen kauttaviivan jälkeen.
• = OIKEA (A2, LEN (A2) -FIND (“@”, SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””)))), 1 )) - Nyt tämä yksinkertaisesti antaisi meille merkkijonon viimeisen kauttaviivan jälkeen.

Hahmon viimeisen sijainnin saaminen mukautetun toiminnon (VBA) avulla

Vaikka yllä oleva kaava on loistava ja toimii kuin viehätys, se on hieman monimutkainen.

Jos viihdyt VBA: n käytössä, voit käyttää mukautettua toimintoa (jota kutsutaan myös käyttäjän määrittämäksi funktioksi), joka on luotu VBA: n kautta. Tämä voi yksinkertaistaa kaavaa ja säästää aikaa, jos joudut tekemään tämän usein.

Käytämme samaa URL -osoitteiden tietojoukkoa (kuten alla):

Tässä tapauksessa olen luonut funktion nimeltä LastPosition, joka löytää määritetyn merkin viimeisen sijainnin (joka tässä tapauksessa on vinoviiva).

Tässä on kaava, joka tekee tämän:

= OIKEA (A2, LEN (A2) -LastPosition (A2, "/")+1)

Näet, että tämä on paljon yksinkertaisempi kuin mitä käytimme yllä.

Näin se toimii:

• LastPosition - joka on mukautettu toimintomme - palauttaa vinoviivan sijainnin. Tämä toiminto sisältää kaksi argumenttia - soluviittauksen, jossa on URL -osoite, ja merkin, jonka sijainnin meidän on löydettävä.
• OIKEA -funktio antaa sitten kaikki merkit eteenpäinviivan jälkeen.

Tässä on VBA -koodi, joka loi tämän toiminnon:

Toiminto LastPosition (rCell As Range, rChar As String) 'Tämä toiminto antaa määritetyn merkin viimeisen sijainnin' Tämän koodin on kehittänyt Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) I = rLen To 1 Vaihe -1 Jos keskitaso (rCell, i - 1, 1) = rChar Sitten LastPosition = i Poistu toiminnosta Loppu Jos seuraava i Lopeta

Jotta tämä toiminto toimisi, sinun on sijoitettava se VB -editoriin. Kun olet valmis, voit käyttää tätä toimintoa kuten mitä tahansa muuta tavallista Excel -toimintoa.

Voit kopioida ja liittää tämän koodin VB-taustaosaan seuraavasti:

Voit asettaa tämän koodin VB Editoriin seuraavasti:

1. Siirry Kehittäjä -välilehdelle.
2. Napsauta Visual Basic -vaihtoehtoa. Tämä avaa VB -editorin taustaohjelmaan.
3. Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia VB Editorin Project Explorer -ruudussa, johon haluat lisätä koodin. Jos et näe Project Exploreria, siirry Näytä -välilehteen ja napsauta Project Explorer.
4. Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää moduuliobjektin työkirjaasi.
5. Kopioi ja liitä koodi moduuli -ikkunaan.

Nyt kaava olisi käytettävissä kaikissa työkirjan laskentataulukoissa.

Huomaa, että sinun on tallennettava työkirja .XLSM -muodossa, koska siinä on makro. Jos haluat, että tämä kaava on käytettävissä kaikissa käyttämissäsi työkirjoissa, voit joko tallentaa sen henkilökohtaiseksi makro-työkirjaksi tai luoda siitä lisäosan.

Saatat pitää myös seuraavista Excel -opetusohjelmista:

• Kuinka saada sanamäärä Excelissä.
• Kuinka käyttää VLOOKUPia useiden ehtojen kanssa.
• Etsi haun viimeinen esiintymisarvo luettelolle Excelissä.
• Pura alimerkkijono Excelissä.