Has anybody got a simple way of converting in Excel a text string showing for example "33.00-" into a negative number. Normally I just multiple the text by 1 and it converts it into a number, but it doesn't work on negatives.
Suggestions would be welcome.
Paul
Paul Olson
Replies (10)
Please login or register to join the discussion.
how is the data being imported into excel?
If it is a csv format text file which you open in excel using the text import wizzard as a csv file, the inverted commas should be ignored by excel and the data imported as positive and negative values.
Text to columns?
You could try using text to coulumns (data, text to coulumns) selecting delimited and choosing " as the delimiter.
This will ususlly strip out leading and trailing blanks and retain the sign.
For a free Addin
look at
http://www.cpearson.com/excel/download.htm
and the Case Convert addin there.
It allows you to perform various functions on a selected range of cells including some maths functions