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

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.