problem with test to columns feature identifying odd delimiting character

[mark_jm via OfficeKB.com]

I have exported some stuff from an outlook inbox into excel for analysis.
The boby of the emails I have exported contain data which I need, each piece
of data is on a seperate row in the email boby.

"CDControls.iAD
AnaBACK PRSR MBAR HIGH
WARNING !!
Reg No: TA656
Serial No: 666069
BATT: 26.9 volts
GSM Sig: 20 db
MEM:5481/86400
"
When I export it it comes in to excel all in one cell with a character
seperating each row that I cant reproduce. Its 2 very bold letter "l"'s which
seems to the the character that forces the new row i the email. How can I
identify the character to properly seperate each row above into seperate
columns in the spreadsheet?

All help appreciated

Thanks

Mark

 

[Ron Rosenfeld]

I have exported some stuff from an outlook inbox into excel for analysis.
The boby of the emails I have exported contain data which I need, each piece
of data is on a seperate row in the email boby.

"CDControls.iAD
AnaBACK PRSR MBAR HIGH
WARNING !!
Reg No: TA656
Serial No: 666069
BATT: 26.9 volts
GSM Sig: 20 db
MEM:5481/86400
"
When I export it it comes in to excel all in one cell with a character
seperating each row that I cant reproduce. Its 2 very bold letter "l"'s which
seems to the the character that forces the new row i the email. How can I
identify the character to properly seperate each row above into seperate
columns in the spreadsheet?

All help appreciated

Thanks

Mark

One way.

A1: Your import
A2: =MID($A$1,ROWS($1:1),1)
B2: =CODE(A2)

Select A2:B2 and fill down at least LEN(A1) rows.
--ron

 

[Eduardo]

Hi,
highlight the cell , data, text to columns, here you have 2 options if you
use Delimited, in the 2nd screen will ask you for the character to identify
in order to split the cell in columns, if you use Fixed with, you will have
to click where you want the column to be

 

[mark_jm via OfficeKB.com]

I have exported some stuff from an outlook inbox into excel for analysis.
The boby of the emails I have exported contain data which I need, each piece

[quoted text clipped - 20 lines]

Mark

One way.

A1: Your import
A2: =MID($A$1,ROWS($1:1),1)
B2: =CODE(A2)

Select A2:B2 and fill down at least LEN(A1) rows.
--ron

Thansk for your insight sonnds promising, but your suggestion returned
coulmn a and b as fllows( just and extract here )

C 67
D 68
C 67
o 111
n 110
t 116
r 114
o 111
l 108
s 115
. 46
i 105
A 65
D 68
"
" 13
"
" 10
S 83
T 84


how does this help?

Mark

 

[Ron Rosenfeld]

I have exported some stuff from an outlook inbox into excel for analysis.
The boby of the emails I have exported contain data which I need, each piece

[quoted text clipped - 20 lines]

Mark

One way.

A1: Your import
A2: =MID($A$1,ROWS($1:1),1)
B2: =CODE(A2)

Select A2:B2 and fill down at least LEN(A1) rows.
--ron

Thansk for your insight sonnds promising, but your suggestion returned
coulmn a and b as fllows( just and extract here )

C 67
D 68
C 67
o 111
n 110
t 116
r 114
o 111
l 108
s 115
46
i 105
A 65
D 68
"
" 13
"
" 10
S 83
T 84


how does this help?

Mark

Your delimeter is the <Carriage Return><Line Feed> sequence <CR><LF>
represented in the above as the 13 10 sequence. I suspect if you examine
closely you will see that sequence at each line break. And that in Excel, the
symbol will be different than the quote mark showing above, and that there will
not be a blank row preceding each 13 and each 10.

There are a number of ways to handle this, depending in part on whether this
will be a one time analysis, or done multiple times.

Manually:

1. Remove one of the delimiters.

A1: Your Data
B1: =SUBSTITUTE(A1,CHAR(13),"")
Select B1
Edit/Copy
Select C1
Edit/Paste Special/Values

2. Use the Text to Columns wizard to split into columns.

Select C1
Data/Text-to-columns
Step 1:
Delimited
<Next>
Step 2:
Clear all boxes
Text qualifier: set to {none}
Select Other
with cursor in the box next to other, while holding down the
<alt> key, type 010 on the **Numeric Keypad** (not on the number keys above the
regular keyboard). Then release the <alt> key

<Next>

Step 3:
Format (or skip) columns if necessary
<Finish>

If this is going to be a repeated task, I would use a VBA macro, but for just
once or twice, the above is probably simplest and quickest.

--ron