0

I am trying to import a TAB delimited file with the following format into Excel 2013

"Column A"          "Column B"
"tjc0056421151r"    "I\gs]hü"nsf^f\F*lqog\düûWp"
"tjc0058088269r"    "EZacð0jrzmnbofofBÖÞRlXZi")["

When importing into Excel, the following format appears

Column A        Column B
tjc0056421151r  I\gs]hünsf^f\F*lqog\düûWp"
tjc0058088269r  EZacð0jrzmnbofofBÖÞRlXZi)["

Example #1

In B1 you will notice that the " has disappeared here:

hü"ns

and is now

hüns

Example #2

And in B2 you will notice that the " has disappeared here:

XZi")["

and is now

XZi)["

Excel has assumed that because there is a " in the value, that this is the closing " of the value, and has therefore stripped it. The actual " at the end of the value, which should have been stripped, has remained.

Is there any option in Excel which prevents this from happening?

1 Answer 1

1

You want to disappear start and ending " and others to remain?

Option 1

Use text editor with RegExp to text file (for example: Notepad++). Find and replace (sect option to allow RegExp) Find: ^\"(.+)"\t"(.+)\"$ Replace: $1\t$2 Now you can import without text qualifier.

Option 2

Import without text qualifier selected and use MS Office RegExp (in non-English version can be different syntax!).

Option 3

Import without text qualifier and use Excel functions to remove first and last ".

=MID(A1,2,LEN(A1)-2)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .