Deze zelfstudie laat u zien hoe u een tekstreeks in een enkele cel naar meerdere kolommen converteert met behulp van de Range TextToColumns-methode in VBA
Tekst naar kolommen
De Bereik.TekstNaarKolommen methode in VBA is een krachtig hulpmiddel voor het opschonen van gegevens die zijn geïmporteerd uit bijvoorbeeld tekst- of csv-bestanden.
Beschouw het volgende werkblad.
De gegevens zijn allemaal in één kolom in Excel binnengekomen en worden gescheiden door aanhalingstekens.
U kunt de methode Range TextToColumns gebruiken om deze gegevens in kolommen te scheiden.
TextToColumns-syntaxis
uitdrukking.TekstNaarKolommen (Bestemming, Data type, TextQualifier, OpeenvolgendeDelimiter, Tab, Puntkomma, Komma, Ruimte, Ander, andereChar, Veldinfo, Decimaalscheidingsteken, DuizendenScheidingsteken, TrailingMinusNummers)
Uitdrukking
Dit is het bereik van cellen die u wilt splitsen - bijvoorbeeld: Bereik ("A1:A23").
Alle argumenten in de methode TextToColumns zijn optioneel (er staan vierkante haken omheen).
Bestemming
Waar u het resultaat wilt hebben - vaak overschrijft u de gegevens en splitst u deze op dezelfde locatie.
Data type
Het type tekstontleding dat u gebruikt - het kan ofwel: xlGescheiden (standaard indien weggelaten), of xlVaste Breedte.
TextQualifier
Als u aanhalingstekens (enkel of dubbel) hebt rond elk veld in de tekst die u splitst, moet u aangeven of ze enkel of dubbel zijn.
ConsequtiveDelimiter
Dit is waar of onwaar en vertelt VBA om 2 dezelfde scheidingstekens samen te beschouwen alsof het 1 scheidingsteken is.
Tab
Dit is ofwel Waar van vals, de standaard is niet waar - dit vertelt VBA dat de gegevens worden begrensd door een tabblad.
Puntkomma
Dit is ofwelWaar van vals, de standaard is niet waar - dit vertelt VBA dat de gegevens worden gescheiden door een puntkomma.
Ruimte
Dit is ofwel Waar van vals, de standaard is niet waar - dit vertelt VBA dat de gegevens worden begrensd door een spatie.
Ander
Dit is ofwel Waar van vals, de standaard is niet waar. Als u dit instelt op True, dan is het volgende argument, andereChar moet worden gespecificeerd.
andereChar
Dit is het teken waarmee de tekst wordt gescheiden (bijvoorbeeld: of |).
Veldinfo
Dit is een array met informatie over het type gegevens dat wordt gescheiden. De eerste waarde in de array geeft het kolomnummer in de gegevens aan en de tweede waarde geeft de constante aan die u gaat gebruiken om het gewenste gegevenstype weer te geven.
Een voorbeeld van voor 5 kolommen met datatypes tekst, getallen en datums zou kunnen zijn:
Matrix(Array(1, xlTextFormaat), Array(2, xlTextFormaat), Matrix(3, xlAlgemeenFormaat), Matrix(4, xlAlgemeenFormaat), Matrix(5, xlMDYFormaat))
Een andere manier om dit vast te stellen is:
Array (Array (1, 2), Array (2, 2), Array (3, 1), Array (4, 1), Array (5, 3))
De getallen in de tweede kolom zijn de waarden van de constanten waarbij de constante xlTextFormat de waarde 2 heeft, de xlGeneralFormat (standaard) de waarde 1 en de xlMDYFormat de waarde 3.
Decimaalscheidingsteken
U kunt het decimale scheidingsteken opgeven dat VBA moet gebruiken als er getallen in de gegevens staan. Als dit wordt weggelaten, wordt de systeeminstelling gebruikt, wat meestal een punt is.
DuizendenScheidingsteken
U kunt het scheidingsteken voor duizendtallen opgeven dat VBA moet gebruiken als er getallen in de gegevens staan. Als het wordt weggelaten, wordt de systeeminstelling gebruikt, wat meestal een komma is.
TrailingMinusNummers
Dit argument is grotendeels voor compatibiliteit voor gegevens die zijn gegenereerd uit oudere systemen waar een minteken vaak achter het nummer stond en niet ervoor. U moet dit instellen op True als negatieve getallen het minteken erachter hebben. De standaard is onwaar.
Tekst naar kolommen converteren
De volgende procedure converteert de bovenstaande Excel-gegevens naar kolommen.
| 12345678910111213141516 | Sub TekstNaarKol1()Bereik ("A1:A25").TextToColumns _Bestemming:=Bereik("A1:A25"),Gegevenstype:=xlGescheiden, _TextQualifier:=xlDoubleQuote, _ConsecutiveDelimiter:=True, _Tabblad:=Fout, _Puntkomma:=Onwaar, _Komma:=Fout,Spatie:=Waar, _Anders:=Fout, _FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _Decimaalscheidingsteken:="." , _DuizendenScheidingsteken:=",", _TrailingMinusNumbers:=TrueEinde sub |
In de bovenstaande procedure hebben we alle parameters ingevuld. Veel van de parameters zijn echter ingesteld op false of op de standaardinstelling en zijn niet nodig. Een schonere versie van de bovenstaande procedure wordt hieronder uiteengezet. U moet de parameternamen gebruiken om aan te geven welke parameters we gebruiken.
| 1234567 | Sub TekstNaarKol2()Bereik ("A1:A25").TextToColumns _Gegevenstype:=xlGescheiden, _TextQualifier:=xlDoubleQuote, _ConsecutiveDelimiter:=True, _Ruimte:=Waar,Einde sub |
Er zijn slechts 4 parameters die daadwerkelijk nodig zijn - de gegevens worden gescheiden door een dubbele aanhalingstekens, u wilt dat opeenvolgende aanhalingstekens als één worden behandeld en de gegevens worden gescheiden door een spatie!
Voor een nog snellere regel code zouden we de parameternamen kunnen weglaten, maar dan zouden we komma's moeten plaatsen om de plaats van de parameter op te slaan. U hoeft alleen informatie in te voeren tot aan de laatste parameter die u gebruikt - in dit geval de spatie die de gegevens scheidt, wat de 8e parameter is.
| 123 | Sub TekstNaarKol3()Bereik ("A1:A25").TextToColumns , xlDelimited, xlDoubleQuote, True, , , , TrueEinde sub |
Nadat u een van de bovenstaande procedures heeft uitgevoerd, worden de gegevens gescheiden volgens de onderstaande afbeelding.
