Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013
Főleg importált adatok esetén fordul elő gyakran, hogy egy oszlopban minden adat aposztróffal kezdődik. Ezt arra használják, hogy mindenképpen szöveges formátumú legyen az adott oszlop. Extrém esetben az üres cellák is tartalmaznak legalább egy aposztrófot. Megszabadulni ezektől nem is olyan egyszerű, de a most ismertetett műveletsorral megoldható.
Alap probléma
Ha egy cellába mindenképpen szövegesen akarunk tárolni egy adatot, legyen az akár szám, legegyszerűbb, ha az adat begépelését egy aposztróffal kezdjük. Ez arra utasítja az Excel-t, hogy szövegként értelmezze az adatot. Az aposztróf a cellában nem látszik, csak a szerkesztőlécen.
Aposztróf törlés probléma
Ha csak számokat tartalmaz az adott oszlop, akkor általában egyszerű az átalakítás, több megoldás is van rá, amiket leírtam egy korábbi anyagban.
Viszont ha törölni szeretnénk az aposztrófot, a szövegek elől, és főleg az üres cellákból, hogy azok valóban üresek legyenek, akkor más megoldás után kell nézni.
Megoldás
Az aposztróf nagyon érdekesen viselkedik. A cella hosszába(karakterszámába nem számít bele, viszont ha csak egy üres aposztróf van egy cellában, az nem számít üresnek, de a hossza nulla. Érdemes megvizsgálni a HOSSZ (LEN) és az ÜRES (ISBLANK) függvényekkel. A HOSSZ nullát ad, az ÜRES viszont HAMIS-t.
Van még egy érdekesség. Ha egy aposztróffal kezdődő cella mellé beírjuk ugyanazt aposztróf nélkül (szöveg, nem szám), majd érték beillesztéssel ráírjuk az aposztrófos verzióra, az aposztróf megmarad!
Aposztróf eltávolítás képlettel
Ha az aposztrófok az „A” oszlopban vannak, és az adatok az A2-ben kezdődnek, akkor egy segédoszlopba adjuk meg ezt a képletet:
JOBB(A2;HOSSZ(A2)) RIGHT(A2;LEN(A2))
Ez jobbról levesz annyi karaktert, amilyen hosszú a szöveg. Ennek az eredménye maga az eredeti szöveg aposztróf nélkül.
Ezután a következőket kell csinálni
- A segédoszlopot saját magára értékként ráírni
- Kitörölni az eredeti, aposztrófos oszlop tartalmát
- A segédoszlopot átmásolni az eredeti helyére
- Segédoszlopot törölni.
Még mindig nem teljesen jó!?
Ez majdnem teljesen jó, de egy kicsike probléma marad. Azok a cellák, amik eredetileg csak egy üres aposztrófot tartalmaznak, nem lesznek üresek ezután sem. Nem látszik már az aposztróf, de mégsem üres. Azoknak, akik mindenképpen üres cellákat szeretnének az aposztrófok helyett, egy kicsit még tornázni kell.
Azt kell megvizsgálni, hogy ha egy cella eredetileg csak aposztrófot tartalmaz, a hossza nulla lesz. A nulla hosszúságú cellák helyére egy speciális értéket kell írni (Én a * karaktert választottam), majd erre a speciális értékre szűrve, vagy keresés után ezeket kijelölve törölni a cellák tartalmát. Tehát a fenti képlet ennek megfelelően módosul:
=HA(HOSSZ(A2)<>0;JOBB(A2;HOSSZ(A2));"*") =IF(LEN(A2)<>0;RIGHT(A2;LEN(A2));"*")
Ha ez a képlet megvan a segédoszlopban, akkor szűrünk vagy rákeresünk a csillagokra, és töröljük az eredményben szereplő cellák tartalmát. Ezután a fenti műveletsor már teljesen jó eredményt ad.
Mindez működés közben
Aposztróf eltávolítás
„ Ha egy aposztróffal kezdődő cella mellé beírjuk ugyanazt aposztróf nélkül (szöveg, nem szám), majd érték beillesztéssel ráírjuk az aposztrófos verzióra, az aposztróf megmarad!”
Nagyon jo cikk/video, viszont ez nalam nem mukodik (excel 2010). Pedig jo lenne..
Van egy “aposztofos” listam szamokkal (zold haromszoggel), amit boviteni szeretnek es a VLOOKUP miatt az uj ertekek is aposztofosak kellene legyenek. Nem tudom mindegyiket szamma atalakitani, mert bizonyos ertekek 0-val kezodnek.
Ez csak ugy mukodik nekem, hogy van egy segedcellam, amibe beirok egy aposztofot, majd ezt kombinalom (&) azzal a cellaval, ami a szamokat tartalmazza (szam formatumban), majd CTR+C es CTRV ertekkent. Van megoldas arra, hogy egy mar meglevo szam formatumu listat “visszaalakitsak” szoveg formatumuva? Egy meglevo szamokat tartalmazo listan (“general” formatumu volt) a “szoveg” cellaformazasra valtas nem segit utolag, csak akkor ha eloszor kivalasztom a szoveg formatumot, majd utana irom be a szamokat, akkor latom szoveg formatumukent (a zold haromszoggel).
Masik kerdes: miert van az, hogy az eredeti listam szovegkent van tarolva, viszont a szerkesztolecen nem latom az aposztofot? Az uj (hozzaadott, kombinalt) ertekeknel viszont latom a szerkesztolecen az aposztofot. A VLOOKUP mukodik mindket verzional.
Ha kell, akkor at tudom kuldeni a fajlt is.
Koszonom a segitseget!!
Meg egy kerdes: hogy tudom azt beallitani a text-to column-nal, hogy a 0 ne tunjon el a harmadik oszlopban szereplo erteknel? A cellaformatumnal elore beallitott text sajnos nem segitett 🙁
EUR | 582,7 | 094236 | State Grant Deduction Cost EUR
Szia!
Megpróbálok válaszolgatni a kérdésekre:
1. Az aposztróf megmaradós rész nekem működik, szintén 2010-es verzióban.
2. Ha a cellában is látni szeretnéd az aposztrófot, akkor kezd a begépelést kettő (2) aposztróffal. Az első aposztróf csak átalakítja a tartalmat szöveg formátumúvá. Ez legtöbbször elegendő
3. General-ról Text formátumra utólag a Text to Columns paranccsal tudsz csinálni.. Első lépésben: Delimited, második lépésben: Nincs elválasztó jel (minden pipát kiveszel),. harmadik lépésben Text formátumot állítasz. ezután meg fognak jelenni a zöld háromszögek, de nem lesznek aposztrófok.
Ez egyben válasz a következő kérdésedre a zöld háromszögekről.
4. ha szöveget alakítasz számmá, akkor mindenképpen el fog tűnni a vezető nulla. ha a formázás miatt meg akarod jeleníteni, akkor egyedi számformátumot állíts be neki. Pl. ha négy karakteren akarod ábrázolni, akkor „0000”, azaz négy darab nulla legyen a formátum.
Egy kis olvasnivaló erről
Koszonom szepen a segitseget!! Tovabbi szep estet! 🙂