VBA - TextToColumns separating text at line breaks

[jmapark91]

Hi,

I need to implement a vba script that will take an excel sheet where column A contains information about an email, such as sender email, subject, body, etc.
There is only 1 column, and I want to split this column into separate columns, one column being the sender's email address, others being subject, body, etc.

Right now, column A has the character "þ" separating the two information, and I want to use this character as a delimiter to call the TextToColumns function.

TextToColumns is not quite working properly. If I do not write DataType:=xlDelimited, it will take 'space' as a delimiter even if I write space:=false, and if I do write DataType:=xlDelimited, it will not only take "þ" as the delimiter, but it also seems to ignore everything after a linebreak, and I lose a good chunk of information.

Is there a way to fix this issue? Maybe if it is not doable with a TextToColumns function, is there a different way I can achieve this?

[HaHoBe]

Hi, jmapark91,

why don´t you include a sample of how your data really looks like and the code you havbe so far? Did you consider to record a macro to show the syntax?

Please Login or Register  to view this content.

You might need to adapt the number of fields and the character for the delimiter but this code was from a macro recording (slightly re-arranged).

Ciao,
Holger

[jmapark91]

Hi HaHoBe,

Thanks a lot for the quick reply.
My data looks like this:

Column A
Sender þ Sender Address þ To þ Received þ Modified þ Categories þ Subject þ Body
Bob M. þ [email protected] þ Andrew þ 11/10/2014 9:52:39AM þ þ Red Category þ Test Email þ Test line breaks. Line 1 \n Test line breaks. Line 2

All this data is in column A (with thousands of rows - each row being a different email), and I need to split this into 8 separate columns.
The first 7 columns seem to work fine, it's just the last column which contains the body of the email that is having the issue.

Here I indicated a new line with "\n" (I didn't know how else to explicitly show this) and the below code will grab the very first line of the body and put it in column 8, but strip and lose the rest of the body content.

Here's the code so far..

Sub infoToColumns() 'to split email info into separate columns

With Range("A1:A8", Range("A" & Rows.Count).End(xlUp))
.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="þ"
End With

End Sub

Thanks again for the help!

[HaHoBe]

Hi, jmapark91,

please have a look at Forum Rule#3 to understand why you are asked to apply code-tags to your procedure.

I put your lines

Sender þ Sender Address þ To þ Received þ Modified þ Categories þ Subject þ Body
Bob M. þ [email protected] þ Andrew þ 11/10/2014 9:52:39AM þ þ Red Category þ Test Email þ Test line breaks. Line 1 \n Test line breaks. Line 2

and using a slightly modified code

Please Login or Register  to view this content.

I received

Sender      Sender Address     To          Received              Modified      Categories      Subject      Body 
Bob M.      abc@gmail.com      Andrew      11/10/2014 9:52:39AM                Red Category    Test Email   Test line breaks. Line 1 \n Test line breaks. Line 2  

Ciao,
Holger

[jmapark91]

Hi,

Again, I tried the code and it gives the same result... the body of the email looks like this

Test line breaks. Line 1
Test line breaks. Line 2

When I run the code however, my last column only has the first line and not the second..

Test line breaks. Line 1