string (123) in Excel csv file

Question

I am creating csv file for importing into Excel. Some values are strings (123).

I need them to show as (123). Excel shows them as -123.

Which characters can I add to (123) to make Excel show them as (123), without any need for post-import manual formatting ?

Tried double-quotes, did not help. Thanks.

Orthohydrogen

Have you got the brackets round the numbers? If so take them off. you could also set the column type to string. Or try single quotes? – Feeder

Answer 1

If you store it as ="(123)" excel will store it as text. Make sure not to leave any space preceding the equals sign.

for example: "data",="(123)","more data"

Anaconda

As mentioned elsewhere, a bug in Excel 2007 means a comma in the string would fail to import properly; you need "=""(4,123)""" to handle that case. – Infold

Answer 2

Try a leading single quote -- '

Cristie

That does show it as (123), but creates warning with explanation mark saying "Number fomatted as string" – Orthohydrogen

Probably, but it's the most correct way to save something like that if you're going to have mixed data types. The ="(123)" idea above is good, but if you're going to do any data manipulation (search / find & replace / etc.) you're going to run into trouble... – Cristie

If you can explain meaning of '... vs ="..." and why warning appear near '... ? – Orthohydrogen

Adding a leading single quote ( ' ) forces Excel to store the rest of the stuff as pure text. Excel would normally think that the (123) is negative 123. If you mark it as text with a leading quote, Excel thinks you're accidentally storing numbers in there and gives you an error (since you wouldn't be able to do any math on it). It's a weird error, but it's also meaningless... – Cristie

A leading ' does not work when reading from a CSV file - it creates an apostrophe in the data. – Infold

Answer 3

Did you try +123 instead? I tried the following data and it works fine.

Name,Number
Howard,+123

Anemic

writing +(123) would also work but i belive what he really wants isn't to import the values as numbers but as text in their original form -> (123) should be the content of the cell. – Eiten

I agree, my misunderstanding. :) – Anemic

Answer 4

I had the same problem. I created a csv file in a web application. The file contained strings and numbers. The problem was that some of the strings where interpreted as numbers by Excel (like an employee id 12345).

The solutions I've found is to add a ALT-255 symbol to every string. It looks as a space to the human viewer, but Excel handles it different. The string setup in this way , will be handled as a string.

I hope someone can use this tip!

Dictate