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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.