Multiple Delimiters

[Doug Mc]

I'm trying to import a text file into a blank xls file.
It has delimters of bars (I guess - looks like an l with nothing at the top
or bottom.)
It works fine if there's only one delimiter but if there are two or more
together I can't get the columns specified.

How do I do that?

Thanks,
Doug

 

[Dave Peterson]

There's an option to "treat consecutive delimiters as one" on that text to
columns wizard (step 2 of 3???).

Maybe you can check it or uncheck it (depending on how you want it treated).

 

[Doug Mc]

Hi Dave,
I did that. Are the delimiters going to still show up - they do when I tell
it to treat them as on.

Doug

 

[Doug Mc]

Here are a couple of records:
609753|A|ABBASZADEH|PAYMAN|||2120|||LANSDOWN|DR|||CARROLLTON|75010|||||||||M|1984|06/30/2002|06/30/2002|0200|01|024|009|065|002|014|006|||006||004||||||||8GEN|E|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2GEN|P||||||||||||||||||||||||||||||||||||||||||||||||||||||||LAURA|05/27/2008|1090374066|0|Y
719724|S|ABERLE|JAMES|||4255|||LAVACA|TRL|||CARROLLTON|75010|||||||||M|1968|05/21/2005|05/21/2005|0200|01|024|009|065|002|014|006|||006||004|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||VEMACS_PERIODIC|08/20/2009|1090374370|0|Y


Doug

 

[Dave Peterson]

It looks like you don't want to treat the consecutive | delimiters as one. Then
each field would be added to its own column.

I saved your data as a text document (Test.txt) and opened it via file|open (in
xl2003).

I saw the text to columns wizard and chose delimited, other and typed in that
vertical bar.

Everything worked perfectly.

If that's what you tried (at least twice!) and it didn't work, then maybe the |
character isn't what got posted in the message.

You may be able to open the file in NotePad and copy that single character.

Then close notepad and try pasting the character into that Other box on the text
to columns wizard.

 

[Doug Mc]

Thanks Dave,
I think my problem is that I didn't type in the bar in the Other box.
It doesn't appear on my keyboard.
I'll try what you said - Notepad, copy - back to the xls file.

Doug

 

[Doug Mc]

The bar character was my problem. I found one and copied it and things look
great!
Thanks for your help - and patience!

Doug

 

[L. Howard Kittle]

I think my problem is that I didn't type in the bar in the Other box.
It doesn't appear on my keyboard.

Take a look at the key three keys to the right of the letter P, and below
the BACKSPACE key.

Try Shif + 'that key' and see if it gives you a vertical bar.

HTH
Regards,
Howard

 

[Dave Peterson]

That vertical bar is the broken vertical bar on my (USA) keyboard.

It's directly above the enter key and on the same key as the \ (backslash).

Thanks Dave,
I think my problem is that I didn't type in the bar in the Other box.
It doesn't appear on my keyboard.
I'll try what you said - Notepad, copy - back to the xls file.

Doug

 

[Gord Dibben]

The "bar" is actually two vertical bars known as a "pipe" and is located
above the Enter key.

It is the backslash key SHIFTED

SHIFT + key


Gord Dibben MS Excel MVP

The bar character was my problem. I found one and copied it and things look
great!
Thanks for your help - and patience!

Doug