text-to-column http://www.google.com

[lezawang]

Hi
When I tried to text-to-column http://www.google.com using / as a delimiter, excel changed the first / to a column but for the second one excel entered an empty column. I know I can get rid of that column if I check [] treat consecutive delimiters as a one
but why excel change the second / to an empty column? thank you

 

[Joe4]

It didn't, it did exactly what you told it to. It didn't change the second delimiter to a blank, it treated it as the second delimiter.
If you choose the "/" as the delimiter, that means that every instance of "/" denotes a new field.
So, if there are two consecutive ones in a row, then that denotes that there is a blank/null field/empty there (nothing in it).
What else would you expect it to return?

 

[lezawang]

Thanks for your reply. I was expecting this result:

http: | /www.google.com

the first / replaced with a column but the second one left alone.

 

[lezawang]

Actually what you have just said makes perfect sense. thank you.

 

[Rick Rothstein]

Thanks for your reply. I was expecting this result:

http: | /www.google.com

the first / replaced with a column but the second one left alone.

A delimiter is a delimiter... there is no picking and choosing among them.

 

[Joe4]

A delimiter is a delimiter... there is no picking and choosing among them.

True, unless you use Text Qualifiers, and have them wrapped up between the Text Qualifiers.

 

[Rick Rothstein]

True, unless you use Text Qualifiers, and have them wrapped up between the Text Qualifiers.

Well, okay, that is true, and it is good that you mention it (I should have but didn't think of it in the context of the OP's question given it would be just as easy to collapse double slashes to single slashes than to modify the double slash to include a text qualifiers).

 

[Joe4]

Well, okay, that is true, and it is good that you mention it (I should have but didn't think of it in the context of the OP's question given it would be just as easy to collapse double slashes to single slashes than to modify the double slash to include a text qualifiers).

Yes, it really was just a follow-up to this:

Thanks for your reply. I was expecting this result:

http: | /www.google.com

the first / replaced with a column but the second one left alone.

and then your reply, as it kind of begs the question, "then how would we get a / to physically show-up"?

It is a constant battle I have had with some of our software companies over the years. The have an Export capability to export CSV files, but data often has has commas in it, and then don't use Text Qualifiers at all, so it is impossible to tell which commas are literal, and which are Text Qualifiers. I try to tell them that if there are literal commas in the Text fields, they need to enclose the Text entry in Text Qualifiers, and they argue with me that they don't, and they are exporting perfectly fine CSV files. Drives me crazy!!!