Numeroiden poistaminen Excelistä vasemmalta: se on helppoa!

Joskus työskentelet tietojen kanssa, joita ei ole muotoiltu haluamallasi tavalla. Sinulla voi esimerkiksi olla sekoitus numeroita ja tekstiä solussa, ja sinun on siivottava nämä tiedot.

On olemassa useita tekniikoita numeroiden poistamiseksi Excelissä tekstin vasemmalta puolelta. Jotkut menetelmät toimivat, kun tiedoilla on johdonmukainen kuvio. VBA tai monimutkaisemmat kaavat toimivat vähemmän jäsenneltyjen tietojen kanssa.

Tässä artikkelissa esitellään seitsemän tapaa poistaa numeroita tekstidatan vasemmalta puolelta. Lopussa sinulla on kattava käsitys eri tekniikoista ja siitä, milloin niitä tulee käyttää.

Mennään!

Sisällysluettelo

• Kuinka käyttää Flash Fill -toimintoa numeroiden poistamiseen Excelissä vasemmalta
• Tekstistä sarakkeiksi -ominaisuuden käyttäminen numeroiden poistamiseen
• 5 tekstifunktiota numeroiden poistamiseksi Excelissä vasemmalta
  • 1. MID-toiminto
  • 2. Yhdistä LEN-toiminnon kanssa
  • 3. LEFT- ja SUBSTITUTE-funktioiden yhdistäminen
  • 4. OIKEA- ja LEN-toimintojen käyttäminen
  • 5. REPLACE-toiminnon käyttäminen
• Kuinka käyttää Power Queryä numeroiden poistamiseen vasemmalta
• 3 edistyksellistä tekniikkaa hahmojen määrästä riippumatta
  • 1. Yhdistä apusarake kaavaan poistaaksesi merkit solujen vasemmalta
  • 2. Luo VBA-makro numeroiden poistamiseksi
  • 3. Luo käyttäjän määrittämä funktio
• Lopulliset ajatukset

Kuinka käyttää Flash Fill -toimintoa numeroiden poistamiseen Excelissä vasemmalta

Flash Fill on Excel-ominaisuus, joka voi täyttää kuviot automaattisesti puolestasi. Se on nopea ja helppo tapa poistaa numeroita solujen vasemmalta puolelta.

Tämä menetelmä ei välttämättä aina toimi täydellisesti, mutta se voi olla hyödyllinen yksinkertaisissa tapauksissa. Noudata näitä ohjeita käyttääksesi Flash Fillia:

1. Kirjoita tietojesi viereen tyhjään sarakkeeseen haluttu tulos ilman numeroita (esim. jos "1234omena" on solussa A1, kirjoita "omena" soluun B1).

2. Valitse solu, jossa on uudet tiedot (esimerkissämme B1) ja napsauta nauhan Tiedot-välilehteä.

3. Napsauta Tietotyökalut-osiossa Flash Fill -painiketta.

4. Excel käyttää kuviota automaattisesti poistaakseen numerot viereisten solujen vasemmalta puolelta.

Tämä on Flash Fill -painikkeen sijainti Tiedot-välilehdessä.

Jos haluat käyttää pikanäppäimiä, paina CTRL + E Windowsissa tai CMD + E Macissa.

Flash Fill on kätevä ominaisuus, mutta kuten useimmissa Excelin asioissa, sama tehtävä voidaan suorittaa useilla tavoilla.

Seuraavassa osiossa tarkastelemme, kuinka voit tehdä saman asian "Teksti sarakkeisiin" -ominaisuuden avulla.

Tekstistä sarakkeiksi -ominaisuuden käyttäminen numeroiden poistamiseen

Voit käyttää Excelin "Teksti sarakkeiksi" -ominaisuutta erottaaksesi numerot solun vasemmalta puolelta. Tämä menetelmä kuitenkin olettaa, että solussa olevien numeroiden ja tekstin välillä on johdonmukainen kuvio tai erotin.

Työstetään tietojoukkoa, joka erottaa numerot kirjaimista viivan avulla seuraavasti:

• 1234-omena

• 4321-oranssi

• 9876-päärynä

Tämä on ennen ja jälkeen, jonka haluamme saavuttaa:

Tässä on vaiheittainen opas tämän esimerkkitietojoukon kanssa:

1. Valitse sarake A.

2. Siirry Tiedot-välilehteen ja napsauta ohjattua Tekstistä sarakkeiksi -toimintoa.

3. Valitse rajattu vaihtoehto ja napsauta Seuraava.

4. Poista kaikkien erottimien valinnat (sarkainta voidaan käyttää oletuksena).

5. Tarkista Muu erotin ja kirjoita "-" syöttöruutuun.

6. Valitse sopiva erotin (välilyönti, pilkku jne.).

7. Napsauta Seuraava ja Valmis.

Tiedot jaetaan nyt kahteen sarakkeeseen. Poistetut merkit ovat ensimmäisessä sarakkeessa, kun taas muu teksti on toisessa sarakkeessa. Tämä kuva näyttää kuinka erotin asetetaan:

Seuraavaksi tarkastellaan viittä tekstifunktiota numeroiden poistamiseen Excelissä.

5 tekstifunktiota numeroiden poistamiseksi Excelissä vasemmalta

Jos tiedät kuinka monta merkkiä numerot muodostavat, voit poistaa ne tekstitoiminnolla.

Oletetaan, että solu A1 sisältää merkkijonon " 1234apple ". Ensimmäiset neljä merkkiä ovat numeroita, jotka haluamme poistaa.

On olemassa useita toimintoja, joita voidaan käyttää yksinään tai yhdistettynä numeroarvojen merkkien poistamiseen.

1. MID-toiminto

Voit käyttää MID-funktiota poimimaan sen osan merkkijonosta, joka alkaa viidennestä merkistä.

MID-funktio poimii tietyn osan tekstistä merkkijonon aloituspaikan ja poimittavien merkkien määrän perusteella. MID-funktion syntaksi on:

MID(teksti, aloitusnumero, merkkien määrä)

• teksti: Tekstimerkkijono, joka sisältää merkit, jotka haluat purkaa.

• aloitusnumero: Ensimmäisen merkin sijainti, jonka haluat purkaa.

• num_chars: merkkijonosta erotettavien merkkien määrä.

Voit poistaa vasemmalla olevat numerot seuraavasti:

1. valitse solu B2, joka sisältää funktion ja tulokset.

2. Kirjoita kaava: MID(A1, 5, 7)

Yllä oleva kaava poistaa merkit merkkijonon 5. paikasta.

2. Yhdistä LEN-toiminnon kanssa

Yksi tämän menetelmän haittapuoli on, että tekstimerkkijonosi ei välttämättä aina ole samanpituinen.

Voit käyttää LEN-funktiota laskeaksesi pituuden dynaamisesti. Tämä funktio palauttaa tekstimerkkijonon pituuden.

Voit muokata kaavaa korvaamaan kolmannen parametrin LEN-funktiolla seuraavasti:

=KESKUS(A1, 5, PITKÄ(A1)-4)

Voit kopioida kaavan alas täyttökahvan avulla. Tämä kuva näyttää yllä olevan kaavan käytössä:

3. LEFT- ja SUBSTITUTE-funktioiden yhdistäminen

LEFT-funktio palauttaa tekstimerkkijonon vasemmanpuoleisimmat merkit. Esimerkiksi LEFT("1234omena", 4) palauttaisi arvon "1234".

Tämä on hyödyllistä, kun haluat poimia numerot. Mutta entä jos haluat poistaa ne?

Voit yhdistää LEFT-toiminnon SUBSTITUTE-funktion kanssa korvataksesi numeron tyhjällä merkkijonolla. Tämä on esimerkkimme muoto:

=KORVAA(A1, VASEN(A1, 4), "")

4. OIKEA- ja LEN-toimintojen käyttäminen

OIKEA-funktio palauttaa tekstimerkkijonon oikeanpuoleisimmat merkit. OIKEA-funktion syntaksi on:

OIKEA(teksti, [merkkien_määrä])

• teksti: Tekstimerkkijono, joka sisältää merkit, jotka haluat purkaa.

• merkkien_määrä: (Valinnainen) Tekstin oikealta puolelta poimittavien merkkien määrä. Jos se jätetään pois, se on oletuksena 1.

Esimerkiksi RIGHT("1234omena", 5) palauttaisi arvon "omena".

Voit myös yhdistää RIGHT- ja LEN-toiminnot välttääksesi alfa-merkkien määrän koodaamisen. Tämä oletetaan, että tiedät numeromerkkien määrän.

Esimerkkimme muoto on: =OIKEA(A1, PITKÄ(A1)-4)

5. REPLACE-toiminnon käyttäminen

REPLACE-funktiolla voidaan korvata tietty määrä merkkejä solun vasemmalta puolelta. Tämä on syntaksi:

KORVAUS(vanha_teksti, aloitusnumero, merkkien_määrä, uusi_teksti)

• old_text: alkuperäinen tekstimerkkijono, jota haluat muokata.

• aloitusnumero: paikka vanhassa_tekstissä, josta haluat aloittaa merkkien korvaamisen.

• merkkien_määrä: niiden merkkien määrä vanhassa_tekstissä, jotka haluat korvata.

• uusi_teksti: tekstimerkkijono, jolla haluat korvata määritetyt merkit.

Seuraava kaava on esimerkkitiedoillemme:

=KORVAA(A1; 1; 4; "")

Ja tämä on viimeinen viidestä tekstifunktiostamme, jotka poistavat merkkejä vasemmalta Excelissä.

Useimmissa tapauksissa seitsemän tähän mennessä käsittelemäämme menetelmää pitäisi riittää, mutta jos haluat lisää tehoa, Power Query on vastaus, ja siitä puhumme seuraavassa osiossa!

Kuinka käyttää Power Queryä numeroiden poistamiseen vasemmalta

Power Query on tehokas tietojen muunnostyökalu , joka on saatavana Excelissä. Tässä osassa esitetty menetelmä olettaa, että solussa olevien numeroiden ja tekstin välillä on erotin.

Ensimmäinen vaihe on muuntaa kohdetiedot taulukoksi:

1. Siirry Tiedot-välilehteen.

2. Valitse solualue.

3. Valitse nauhan Hae tiedot -osiosta "Taulusta/alueesta".

Kun Power Query Editor avautuu, toimi seuraavasti:

1. Valitse kohdesarakkeet.

2. Siirry "Muunna" -välilehteen

3. Napsauta nauhassa "Extract" ja valitse "Teksti erottimen jälkeen".

4. Kirjoita "Teksti erottimen jälkeen" -valintaikkunaan viiva (-).

5. Napsauta "OK" hyväksyäksesi tiedot.

Muunnetusta sarakkeesta poistetaan nyt numerot tekstin vasemmalta puolelta.

Napsauta "Sulje ja lataa" ladataksesi muunnetut tiedot takaisin laskentataulukkoon.

Jos olet uusi tietotyökalun käyttäjä, tutustu oppaaseemme osoitteessa .

Jos haluat sukeltaa syvemmälle Power Queryn taulukoiden käyttöön, tässä videossa käydään läpi joitain monimutkaisempia esimerkkejä:

3 edistyksellistä tekniikkaa hahmojen määrästä riippumatta

Tähänastisten menetelmien suurin haittapuoli on, että kaikkien solujen numeroiden tulee noudattaa samaa kaavaa tai olla tietty määrä merkkejä.

Entä jos haluat poistaa merkkejä ilman selkeää kuviota alkuperäisissä tiedoissa? Tämän osan tekniikat on tarkoitettu keskitason Excel-käyttäjille.

1. Yhdistä apusarake kaavaan poistaaksesi merkit solujen vasemmalta

Voit yhdistää funktioita apusarakkeiden kanssa, kun et tiedä numeroiden kokonaispituutta. Tämä menetelmä vaatii kaksivaiheisen prosessin:

• käytä kaavaa luodaksesi väliaikaisesti muokatun version alkuperäisestä tekstistä

• käytä toista kaavaa numeroiden poistamiseen muokatun tekstin perusteella.

Ensimmäinen vaihe on käyttää seuraavaa kaavaa korvataksesi kaikki numerot tietyllä merkillä (esim. "|"):

=KORVAA(KORVAA(KORVAA(KORVAA(KORVAA(KORVAA(KORVAA(KORVAA(A1, “0”, “|”)), “1”, “|”)), “2”, “|”)),),),), “3”, “|”), “4”, “|”), “5”, “|”), “6”, “|”), “7”, “|”), “8”, “ |), "9", "|"

Toinen vaihe on käyttää seuraavaa kaavaa toisessa apusarakkeessa (esim. sarakkeessa C):

=TRIM(OIKEA(KORVAA(B1, "|", REPT(" ", PITKÄ(B1))), PITKÄ(B1)))

Tämä on ennen ja jälkeen sarakkeiden B ja C kaavoilla:

Kun aloitat funktioiden yhdistämisen tällä tavalla, voit kohdata ongelmia valittaessa kaavoihisi vääriä vaihtoehtoja. Artikkelimme auttaa sinua yleisen virheen vianmäärityksessä.

Haluat ehkä myös jakaa laskentataulukon, joka sisältää apusarakkeita. On tärkeää, että kollegasi eivät poista tai muuta välituloksissa olevia kaavoja. Artikkelimme auttaa sinua tuottamaan toiminnallisuuden.

2. Luo VBA-makro numeroiden poistamiseksi

VBA on monipuolinen komentosarjakieli , jonka avulla voit automatisoida tehtäviä ja luoda mukautettuja ratkaisuja Excelissä.

Voit esimerkiksi kirjoittaa makron, joka poistaa numerot kaikkien tietyn sarakkeen tai alueen solujen vasemmalta puolelta.

Voit tehdä tämän seuraavasti:

1. Avaa VBA-editori painamalla ALT + F11 .

2. Luo uusi moduuli napsauttamalla Lisää > Moduuli.

3. Kopioi ja liitä seuraava koodi poistaaksesi vain numerot sarakkeen A vasemmalta puolelta:

Sub RemoveLeftNumbers()
    Dim ws As Worksheet, cell As Range, i As Integer
    
    ' Set the active worksheet as the target worksheet
    Set ws = ActiveSheet

    ' Loop through all the cells in column A
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ' Remove numbers from the left of each cell
        For i = 1 To Len(cell.Value)
            If Not IsNumeric(Mid(cell.Value, i, 1)) Then
                Exit For
            End If
        Next i
        cell.Value = Mid(cell.Value, i)
    Next cell
End Sub

Viimeinen vaihe on makron suorittaminen. Napsauta vihreää kolmiota tai "Suorita" -valikkokohtaa.

3. Luo käyttäjän määrittämä funktio

Voit myös luoda VBA:lla käyttäjän määrittämän funktion, jota voit hyödyntää kaavoissasi.

Voit tehdä tämän seuraavasti:

1. Avaa VBA-editori painamalla ALT + F11.

2. Luo uusi moduuli napsauttamalla Lisää > Moduuli.

3. Kopioi ja liitä seuraava koodi:

Public Function RemoveNumbersFromLeft (text As String) As String
    Dim i As Integer
    For i = 1 To Len(text)
        If Not IsNumeric(Mid(text, i, 1)) Then
            Exit For
        End If
    Next i
    RemoveNumbersFromLeft = Mid(text, i)
End Function

Palaa nyt Excel-taulukkoon ja käytä julkista toimintoa kaavassa.

Tämä kaava toimii esimerkiksi hyväksymällä A1-solun:

=PoistaNumbersFromLeft(A1)

Lopulliset ajatukset

Tässä artikkelissa olemme tutkineet useita tapoja poistaa numeroita tekstijonojen vasemmalta puolelta Excelissä. Jokaisella tekniikalla on omat etunsa ja rajoituksensa.

Mikään yksittäinen menetelmä ei ole täydellinen jokaiseen tilanteeseen, mutta varmasti löytyy lähestymistapa, joka sopii parhaiten tarpeisiisi. Harjoittelemalla opit soveltamaan näitä tekniikoita kaikkien tiellesi tulevien tietojen muotoiluhaasteiden käsittelyyn.

Joten siinä on kaikki, miten voit poistaa ei-toivotut numerot Excelin vasemmalta puolelta. Se saattaa aluksi näyttää hieman hankalalta, mutta oikeilla tekniikoilla pääset johtamaan sitä hetkessä! Onnea numeroiden murskaukseen!