Text to Columns cu Formule

trucuri, sfaturi si alte idei pentru imbunatatirea lucrului cu Excel

Text to Columns cu Formule

Mesajde Catalin B. » Mie Aug 20, 2014 8:49 am

Pentru cei interesati de subiect, anexez o formula ce imita functiunea Text To Columns.
Formula este:
=IFERROR(MID($A4,SUMPRODUCT(LEN(B4:$B4))+COLUMN(A4),FIND($C$1,RIGHT($A4,LEN($A4)-IFERROR(FIND($C$1,$A4,SUMPRODUCT(LEN(B4:$B4))+COLUMN(A4)-1),0))&$C$1)-1),"")
In care , in C1 se introduce separatorul dorit (virgula, spatiu, etc) iar textul este in coloana A, incepand cu randul 4.

Formula poate fi copiata la dreapta si in jos dupa necesitati.

Anexez si un fisier demo. Coloana B trebuie sa ramana nefolosita.

Pentru delimitatori consecutivi, de forma ",," sau 2 spatii consecutive, se poate inlocui in formula referinta catre $A4 cu o formula care sa inlocuiasca delimitatorii consecutivi cu un singur delimitator: ( In Wizard-ul text to columns, exista optiunea "Treat consecutive delimiters as one ")
=SUBSTITUTE($A4,$C$1&$C$1,$C$1) (evident, valabila doar pentru 2 delimitatori consecutivi)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Probleme să fie, că soluţii se găsesc...
Catalin B.
Moderator
Moderator
 
Mesaje: 747
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: Text to Columns cu Formule

Mesajde Indigo » Lun Sep 08, 2014 9:12 am

Se poate si mai simplu....

=TRIM(MID(SUBSTITUTE($A2;"-";REPT(" ";999));(COLUMN(A1)-1)*999+1;999))

Lista este in col A, incepand cu A2, iar formula va fi in B2 sau oriunde este nevoie. Delimitatorul (aici "-" ) poate fi pus intro celula si formula va fi modificata sa indice acea celula.
Aici am pus referinta catre celula C1 (evident poate fi pus in orice celula care nu se afla in zona de extragere a itemurilor).

=TRIM(MID(SUBSTITUTE($A2;$C$1;REPT(" ";999));(COLUMN(A1)-1)*999+1;999))

Formula poate fi "trasa" la dreapta si in jos functie de necesitati.

Formula indicata de Catalin B, poate fi gasita (cu toate explicatiile necesare) aici: http://www.myonlinetraininghub.com/
Indigo
 
Mesaje: 774
Membru din: Sâm Sep 26, 2009 8:05 pm

Re: Text to Columns cu Formule

Mesajde Catalin B. » Lun Sep 08, 2014 9:42 am

Asa este, e plin internetul de formula asta, este varianta optima.
Despre tehnica inlocuirii separatorului sunt mai multe subiecte si la noi pe forum: Extragere articol
Scopul principal e sa reusim sa gasim singuri o solutie la o problema, e mai mult un exemplu de "how to think in excel"
Intr-adevar, formula este prezenta si pe http://www.myonlinetraininghub.com/text ... el-formula si e scrisa tot de mine, anexez si acel fisier, nu este un copy-paste, daca asta era aluzia :).
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Probleme să fie, că soluţii se găsesc...
Catalin B.
Moderator
Moderator
 
Mesaje: 747
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi


Înapoi la Tips and Tricks Excel

Cine este conectat

Utilizatorii ce navighează pe acest forum: Niciun utilizator înregistrat şi 3 vizitatori

cron