Text to Columns cu Formule

trucuri, sfaturi si alte idei pentru imbunatatirea lucrului cu Excel
Catalin B.
Moderator
Moderator
Mesaje: 749
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Text to Columns cu Formule

Mesaj de 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...

Indigo
Mesaje: 774
Membru din: Sâm Sep 26, 2009 8:05 pm

Re: Text to Columns cu Formule

Mesaj de 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/

Catalin B.
Moderator
Moderator
Mesaje: 749
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: Text to Columns cu Formule

Mesaj de 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...

Scrie răspuns

Înapoi la “Tips and Tricks Excel”