Note: The other languages of the website are Google-translated. Back to English
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Odstranite presledke v Excelovih celicah - vodilne presledke, zadnje presledke, dodatne presledke ali vse presledke

Včasih, ko so podatki kopirani in prilepljeni iz drugih aplikacij na delovni list, lahko pride kaj narobe, če so poleg podatkov nezavedno priloženi dodatni presledki. Na primer, ko dodate dve številčni celici, ki vključujeta presledke, bo rezultat vrnil kot 0 namesto pravilnega rezultata; Ali pa dobite napačen rezultat, medtem ko primerjate dve celici z isto vsebino, vendar ena od njih vsebuje presledke, ki vodijo ali zaostajajo. Kako odstraniti te presledke? Ta vadnica vam bo predstavila več metod.


Iz celic odstranite odvečne presledke, vključno s presledki, ki vodijo, zaostajajo in odvečno vmesne presledke

Recimo, da imate seznam stolpcev s celicami, ki vsebujejo presledke, ki vodijo, zaostajajo in presegajo vmesne presledke. Za odstranjevanje vseh teh dodatnih prostorov iz celic vam lahko pomaga funkcija TRIM.

=TRIM(TEXT)

Izberite prazno celico, vnesite =TRIM(B3) vanj in pritisnite Vnesite tipko. In nato povlecite njegovo Ročica za polnjenje navzdol, da uporabite formulo za druge celice. Oglejte si posnetek zaslona:
Opombe: B3 je celica, ki vsebuje besedilo, iz katerega želite odstraniti presledke.

Zdaj se vsi dodatni presledki, vključno z vodilnimi, zaostajajočimi in vmesnimi presledki, odstranijo iz določenih celic.

Opombe: To operacijo je treba izvesti v novem stolpcu. Ko odstranite vse odvečne presledke, morate s pritiskom na zamenjati izvirne podatke z obrezanimi Ctrl + C, izberite izvirni obseg podatkov in z desno miškino tipko izberite Vrednote pod Možnosti lepljenja oddelek.

Več klikov za odstranitev nepotrebnih prostorov iz celic

Z Odstrani presledke samo nekaj klikov vam lahko pomaga odstraniti ne samo vodilni presledki, zadnji presledki, odvečni presledki ampak tudi vseh presledkih iz obsega, več razponov ali celo celotnega delovnega lista, kar vam bo prihranilo veliko delovnega časa.
Kutools za Excel - Zbere več kot 300 zmogljivih naprednih funkcij, zasnovanih za 1500+ delovnih scenarijev, ki rešujejo 80% težav v Excelu.

Prenesite in preizkusite 30-dnevno brezplačno preskusno različico funkcije

 

Iz celic odstranite samo začetne presledke

V nekaterih primerih boste morda morali odstraniti samo vodilne presledke in ohraniti vse vmesne presledke v celicah, kot je prikazano na spodnji sliki zaslona. V tem razdelku je predstavljena druga formula in koda VBA za rešitev te težave.

Uporabite funkcijo MID, da odstranite samo presledke

Izberite prazno celico (tukaj izberem celico D3), skopirajte spodnjo formulo vanjo in pritisnite na Vnesite tipko. In nato povlecite Ročica za polnjenje navzdol, da uporabite formulo za druge celice.

=MID(B3,FIND(MID(TRIM(B3),1,1),B3),LEN(B3))

V tej formuli: B3 je celica, ki vsebuje besedilo, iz katerega želite odstraniti začetne presledke.

Opombe: To operacijo je treba izvesti v novem stolpcu. Po odstranitvi vseh vodilnih presledkov morate prvotne podatke nadomestiti z obrezanimi brez formul.

Uporabite kodo VBA, da odstranite samo presledke

1. Odprite delovni list s celicami, iz katerih boste odstranili presledke, in pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. klik Vstavi > Moduli, nato kopirajte spodnjo kodo VBA v okno modula.

Sub RemoveLeadingSpaces()
'Updateby20190612
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

3. Pritisnite F5 tipko za zagon kode. A Kutools za Excel Pojavi se pogovorno okno, izberite neprekinjene celice, iz katerih boste odstranili vodilne presledke, in kliknite OK gumb.

Zdaj lahko vidite, da so iz izbranih celic odstranjeni samo vodilni presledki.


Iz celic odstranite samo končne presledke

1. Odprite delovni list s celicami, iz katerih boste odstranili presledke, in pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. klik Vstavi > Moduli, nato kopirajte spodnjo kodo VBA v okno modula.

Sub RemoveTrailingSpaces()
'Updateby20190612
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.RTrim(Rng.Value)
Next
End Sub

3. Pritisnite F5 tipko za zagon kode. A Kutools za Excel Pojavi se pogovorno okno, izberite neprekinjene celice, iz katerih boste odstranili končne presledke, in kliknite OK . Zdaj lahko vidite, da so iz izbranih celic odstranjeni samo končni presledki.


Odstranite vse presledke iz celic

Če se želite znebiti vseh presledkov v določenih celicah, vam lahko metode v tem razdelku naredijo uslugo.

S funkcijo SUBSTITUTE odstranite vse presledke iz celic

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Izberite prazno celico, kopirajte spodnjo formulo in pritisnite Vnesite tipko. Nato povlecite Ročica za polnjenje navzdol, da uporabite formulo za druge celice.

=SUBSTITUTE(B3," ","")

nasveti: V tej formuli, B3 je celica z besedilom, za katero želite odstraniti vse presledke;
         "" (presledek, zaprt z dvojnimi narekovaji) tukaj predstavlja presledke, ki jih želite odstraniti;
         "" tukaj pomeni, da boste vse prostore zamenjali z ničemer.

Nato lahko vidite, da se vsi presledki v določenih celicah takoj odstranijo.

Opombe: To operacijo je treba izvesti v novem stolpcu. Po odstranitvi vseh presledkov morate prvotne podatke nadomestiti z obrezanimi brez formul.

Uporabite funkcijo Najdi in zamenjaj, da odstranite vse presledke iz celic

Poleg zgoraj navedenega NAMESTITEV funkcija Najdi in zamenjaj lahko pomaga tudi pri odstranjevanju vseh presledkov iz celic.

1. Izberite celice, ki vsebujejo presledke, ki jih boste odstranili, in pritisnite Ctrl + H tipke za odpiranje Poišči in zamenjaj pogovorno okno.

2. V Ljubljani Poišči in zamenjaj v pogovornem oknu in pod Zamenjaj zavihek vnesite en presledek v Našli kaj besedilo, obdržite Zamenjaj z polje z besedilom prazno in nato kliknite Zamenjaj vse gumb.


Preprosto odstranite vodilne, zadnje, dodatne in vse presledke iz celic s Kutools

Nasvet: Če ste siti uporabe formul in kod VBA za odstranjevanje presledkov, se Odstrani presledke uporabnost Kutools za Excel je tvoja najboljša izbira. Z le nekaj kliki lahko odstranite ne le vodilne presledke, zadnje presledke, odvečne presledke, temveč tudi vse presledke iz obsega, več obsegov ali celo celotnega delovnega lista, kar vam bo prihranilo veliko delovnega časa.

Pred uporabo Kutools za Excel si morate vzeti minute brezplačno ga naložite in namestite najprej.

1. Izberite obseg ali več obsegov s celicami, iz katerih boste odstranili presledke, kliknite Kutools > Besedilo > Odstrani presledke. Oglejte si posnetek zaslona:

2. Opazite, da je v Odstrani presledke pogovorno okno:

  • Če želite odstraniti samo vodilne presledke, izberite Vodilni prostori možnost;
  • Če želite odstraniti samo končne presledke, izberite Zaostali presledki možnost;
  • Če želite hkrati odstraniti vodilni presledek in zadnje presledke, izberite Vodilni in zaključni presledki možnost;
  • Če želite odstraniti vse odvečne presledke (vključno z vodilnimi, zadnjimi, presežki vmesnih presledkov), izberite Vsi odvečni prostori možnost;
  • Če želite odstraniti vse presledke, izberite Vsi prostori možnost.

Nato kliknite na OK za zagon operacije.

  Če želite brezplačen (30-dnevni) preizkus tega pripomočka, kliknite, če ga želite prenestiin nato nadaljujte z uporabo postopka v skladu z zgornjimi koraki.


Drugi praktični primeri, povezani z Excelovimi presledki

Ste se poleg odstranjevanja presledkov iz celic že kdaj srečevali z okoliščinami štetja, dodajanja presledkov ali zamenjave presledkov z drugimi znaki v celicah? Spodnje priporočilo lahko pospeši vaše delo v Excelu.

Šteje skupno število presledkov v celici
Preden odstranite vse presledke iz celice, vas morda zanima, koliko presledkov v njej obstaja. V tej vadnici so podrobno opisane metode, ki vam pomagajo hitro pridobiti skupno število presledkov v celici.
Kliknite, če želite izvedeti več ...

Dodajte prostor za vsako vejico v določenih celicah Excel
Včasih lahko presledke nenamerno odstranite iz določenih celic. Ta vadnica govori o dodajanju presledka za vsako vejico, da bo besedilni niz bolj natančen in standarden s podrobnimi koraki.
Kliknite, če želite izvedeti več ...

Dodajte presledke med številkami v Excelovih celicah
Ta vadnica govori o dodajanju presledka med vsako številko ali vsako nto številko v celicah Excel. Recimo, da imate stolpec telefonskih številk in želite med njimi dodati presledke, da bo številka videti bolj intuitivna in lažja za branje. Pomagale bodo metode v tej vadnici.
Kliknite, če želite izvedeti več ...

Dodajte presledke pred velikimi črkami v celicah Excel
Ta vadnica govori o dodajanju presledka pred vsemi velikimi črkami v celicah Excel. Recimo, da imate seznam besedilnih nizov z naključno odstranjenimi presledki, kot je ta: InsertBlankRowsBetweenData, da dodate presledke pred vsako veliko začetnico, da besede ločite kot Vstavi prazne vrstice med podatke, poskusite z metodami v tej vadnici.
Kliknite, če želite izvedeti več ...

V celicah Excel nadomestite prostor z določenim znakom
V mnogih primerih raje zamenjate presledke s posebnimi znaki, namesto da bi jih neposredno odstranili iz celic. Tu so na voljo metode za enostavno zamenjavo prostora s podčrtajem, pomišljajem ali vejico v celicah.
Kliknite, če želite izvedeti več ...



  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Da345 · 2 years ago
    I removed all spaces with the Replace function, very easy, thank you
  • To post as a guest, your comment is unpublished.
    javad · 2 years ago
    thank you a lot

    it was very god
  • To post as a guest, your comment is unpublished.
    Rafik · 3 years ago
    Thank you a lot.
  • To post as a guest, your comment is unpublished.
    Ocaya · 4 years ago
    Excellent, Very helpful
  • To post as a guest, your comment is unpublished.
    alefpe · 4 years ago
    Thank you very much.
    It was so helpful.
  • To post as a guest, your comment is unpublished.
    alfonso · 4 years ago
    if it doesnt work for you. First you remove the spaces, then you remove the letters, for example: i have USD 1234.00 , first i do the find & replace just the space between USD and 1234.00, now i have USD1234.00, now i go back to find & replace and on find what: i put USD ( no spaces ), and nothing on REPLACE WITH: then i click on Replace all , and i have now 1234.00 if you do it the other way it doesnt work i dont know why.
  • To post as a guest, your comment is unpublished.
    ArvRajB · 4 years ago
    Thank you very much!!
  • To post as a guest, your comment is unpublished.
    Cecep Saefulloh · 4 years ago
    Great Tips especially for research keywords and to make a lot of hashtags on facebook, and You have great tools to make it simple

    It is appropriate that we visit each other and communicate
    Web Development Agency Konsultan Blog Teknologi
  • To post as a guest, your comment is unpublished.
    Joseph Wokwera · 4 years ago
    Thanks so much helpful. i ve been trying this for 2 days. it worked
  • To post as a guest, your comment is unpublished.
    PRAMOD · 4 years ago
    Excellent.Very helpful to remove space in a cell
  • To post as a guest, your comment is unpublished.
    PRAMOD · 4 years ago
    Excellent, nice formula to substitute or remove the spaces in a cell.
  • To post as a guest, your comment is unpublished.
    sachin · 4 years ago
    Thank You Very Much. help me a lot..
  • To post as a guest, your comment is unpublished.
    SUDHIR MISHRA · 5 years ago
    EXCELLENT :) THANK FOR HELP
  • To post as a guest, your comment is unpublished.
    Kosova · 5 years ago
    Thanks a lot, now its so easy.
  • To post as a guest, your comment is unpublished.
    RDM · 5 years ago
    THANK YOU! Really helped me and my bandwidth
  • To post as a guest, your comment is unpublished.
    Sharil · 5 years ago
    Oh its amazing, i didn't knew about to remove the space between words in a cell....its so easy...thanku so much.
  • To post as a guest, your comment is unpublished.
    Ashok Kumar R · 5 years ago
    :-) good.. any move issue please mail me
  • To post as a guest, your comment is unpublished.
    Dinesh · 5 years ago
    i am using MS 2010 ,how to remove all space in excel. exmple also give
  • To post as a guest, your comment is unpublished.
    JUNAID · 5 years ago
    Thanks a lot for helping us!!!!!!!!! :-)
  • To post as a guest, your comment is unpublished.
    JUNAID · 5 years ago
    THANKS A LOT FOR HELPING US!!!!!!!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Suresh · 6 years ago
    Thanks a lot for sharing this Tool :-)
  • To post as a guest, your comment is unpublished.
    KD · 6 years ago
    Thank You. Your help has reduced my effort a lot.
  • To post as a guest, your comment is unpublished.
    Henman · 6 years ago
    Neither of these suggestions work... except, I'm sure, the Kutools solution so conveniently suggested. What an awesome blog. Thanks.
  • To post as a guest, your comment is unpublished.
    Mr.Niekoo · 6 years ago
    How i can remove dots from my columns of excel.I want to remove all dots from full list of columns.The data consist on Phone no's list

    email me
    mr.niekoo@hotmail.com
  • To post as a guest, your comment is unpublished.
    rajesh · 6 years ago
    Great command, It is very usefull.
  • To post as a guest, your comment is unpublished.
    Maria · 6 years ago
    I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?
    • To post as a guest, your comment is unpublished.
      Imran · 5 years ago
      [quote name="Maria"]I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?[/quote]
      Select the column--> go to Data> Text to column> select delimited> next> next> change the date format as MDY or DMY as the case in your sheet> press fininsh.
  • To post as a guest, your comment is unpublished.
    M&M · 6 years ago
    Replace option is the best and easiest! Thanks for sharing the tips!
  • To post as a guest, your comment is unpublished.
    Yasar Arafath · 6 years ago
    Remove space before and after the cell content (Eg:___26350__)
    We can use this formula =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
    • To post as a guest, your comment is unpublished.
      Manish · 6 years ago
      Thanks a lot:):):).... It saved a lot of time....keep on updating such useful and very rate instances!!!!!!
  • To post as a guest, your comment is unpublished.
    Sushen · 6 years ago
    Very use full command
  • To post as a guest, your comment is unpublished.
    carla · 6 years ago
    Thank you! very helpful. saved the day.
  • To post as a guest, your comment is unpublished.
    kirtan · 6 years ago
    tried all the options above. not working as there are spaces before and after number. Any other alternative?
    • To post as a guest, your comment is unpublished.
      Yasar Arafath · 6 years ago
      Try this one

      =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
  • To post as a guest, your comment is unpublished.
    karan · 6 years ago
    very helpful :) and easy to understand

    ;-)
  • To post as a guest, your comment is unpublished.
    Anna · 6 years ago
    Very helpful!
    Thank you
  • To post as a guest, your comment is unpublished.
    GaryMonday · 7 years ago
    Thank you very much for the trick, very very useful, it makes my spreadsheet a lot neater.

    Thanks again.

    Gary
  • To post as a guest, your comment is unpublished.
    Prabhakar · 7 years ago
    Wow its fantastic small commands work very well
  • To post as a guest, your comment is unpublished.
    ilham · 7 years ago
    wow thanks ..useful :-) :):):):):):)
  • To post as a guest, your comment is unpublished.
    BISHNU · 7 years ago
    I like this tools very much
  • To post as a guest, your comment is unpublished.
    KALPESH SUTHAR · 7 years ago
    Thanks a lot....its really helpful..save lot of time... :-)
  • To post as a guest, your comment is unpublished.
    corpsman0000 · 7 years ago
    how do you remove spaces without removing the zeros that in the begining of values middle and end? i just want to remove the spaces in between the values only. i tried the above and the zeros disappeared.
    • To post as a guest, your comment is unpublished.
      Avi · 6 years ago
      Convert the cell into text format then use replace function
  • To post as a guest, your comment is unpublished.
    Ranjith Kumar · 7 years ago
    Thanks Very very use full
  • To post as a guest, your comment is unpublished.
    Eva · 7 years ago
    Thanks for finally talking about >How to remove spaces between character and numbers within cells in Excel?
  • To post as a guest, your comment is unpublished.
    Rebeccah · 7 years ago
    Find/replace doesn't work for me, which is why I'm googling this topic in the first place. Is there a setting somewhere that disables this?

    I want to delete ":" form the cells in a column (and "/" from the cells in another column). I ought to be able to highlight the column, ^H, type ":" (or "/") in the find field, leave the replace field blank, and click Replace All, and it should do it. Or Find Next/Replace/Replace/Replace through the cells one at a time. But it advances therough the cells but doesn't do anything. If I put something in the replace field, it will do the replace, but it won't replace with an empty string.
  • To post as a guest, your comment is unpublished.
    Stefan · 7 years ago
    Thank you, easy to understand. best on the web
  • To post as a guest, your comment is unpublished.
    Janardhan · 7 years ago
    Very easy to understand. Good to follow.
    • To post as a guest, your comment is unpublished.
      ashutosh · 6 years ago
      bullshit....doesn't work
      • To post as a guest, your comment is unpublished.
        Joel · 5 years ago
        Didnt work for me too, however found the right way. Select and highlight the space you want to get rid of. Copy this space and select the find and replace. Paste this space in find and replace with nothing. I guess it wasnt a space in the first place. Whatever it was you now find it by copying whatever it was. Hope it works for you.
        • To post as a guest, your comment is unpublished.
          Ramesh Kumar P · 4 years ago
          It really worked....I tried may function like Clean, Trim, Substitute, etc
        • To post as a guest, your comment is unpublished.
          Jay · 4 years ago
          THAT WORKED! Thanks man
        • To post as a guest, your comment is unpublished.
          kati · 5 years ago
          thanks soo much. you are a genius!!!!
        • To post as a guest, your comment is unpublished.
          Imran · 5 years ago
          [quote name="Erika"]It worked for me! Thank you![/quote]
          Thank you so much for that. This saved a lot of time
        • To post as a guest, your comment is unpublished.
          Erika · 5 years ago
          It worked for me! Thank you!
  • To post as a guest, your comment is unpublished.
    Muhibullah Afzalzada · 7 years ago
    Thanks a lot, the Replace option worked as a charm... you really saved loads of my time.
  • To post as a guest, your comment is unpublished.
    Muhibullah Afzalzada · 7 years ago
    Thanks a lot, I really needed and the Replace option was a charm.
    • To post as a guest, your comment is unpublished.
      Stephen · 4 years ago
      Thanks a lot for the explanation - saved our dept a lot of work.