How to format multiline text that begins with a minus in a CSV?

Trying to create a CSV with a text editor that can be opened in Excel.  Single record in CSV is

- POOR SHAPE

opening that in Excel shows #NAME? error in cell A1.  Changing the CSV to

="- POOR SHAPE"

and all is well - cell A1 shows

- POOR SHAPE

So now I want the text to be multilined. If I put a linefeed (represented as <LF> below) between POOR and SHAPE using the text editor

="- POOR<LF>SHAPE"

and open the CSV with Excel then cell A1 contains ="POOR and A2 contains SHAPE".

So then I try it as

- POOR<LF>SHAPE

and open the CSV with Excel.  A1 contains #NAME? error and A2 contains SHAPE

Now - here's the kicker.  I can enter this in Excel (<LF> is input as ALT-ENTER) - notice the single quote at beginning

'- POOR<LF>SHAPE

and it shows A1 perfectly as multiline text in Excel

- POOR
SHAPE

However, if I save this as the CSV it's saved as

"- POOR<LF>SHAPE"

which then doesn't format properly when opened in Excel (cell A1 shows #NAME? error).

Also tried putting this in the CSV with a text editor

'- POOR<LF>SHAPE

and

'="- POOR<LF>SHAPE"

neither worked.  The single apostrophe only works when inputting in Excel.

Any help would greatly appreciated.  Driving me nuts.

TIA,
Phil :(

 
DESCO1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ExcelEOCommented:
try to use import data feature in excel instead of reading a csv file directly.  The import gives you more control over how Excel interprets the text.  It is a lot easier than formatting your text to the liking of the csv file format of Excel.
0
Rob HensonFinance AnalystCommented:
Have you tried using the longer dash rather than the minus symbol?

Get the longer dash by typing Alt + 0150 with the number keypad.

Short dash code 45
Long dash code 150

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DESCO1Author Commented:
@ExcelEO:  Thanks for the import suggestion but the CSV is going to always be an email attachment so the user will be double clicking it to open it in Excel.  There's no opportunity for it to be imported unless the user saves it disk, opens Excel, and does an import.  Not really what we want the user to go through to open a CSV attachment.

@rob: That's an interesting workaround and will solve my immediate problem, so I will accept it as the solution.  However there's drawback in that once the user opens the spreadsheet they won't be able to successfully search on "- POOR" if they wanted.
0
DESCO1Author Commented:
A very slick workaround with a slight drawback that the text with the beginning long dash isn't searchable by the user if they try to use the short dash as the search character.  Since this data is extracted from a database, the other drawback is that by changing the short dash to the long dash it does mis-represent what was extracted from the database.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.