Excel -How to convert text representing a negative value into a number.

Excel -How to convert text representing a...

Didn't find your answer?

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.

avatar
By AnonymousUser
04th Oct 2003 07:59

More blether!
David makes a good point: preventing the problem rather than curing it is the preferable strategy.

Lawrence, whilst your scheme may work, you are creating work for yourself firstly by getting rid of the quotation marks and then by converting from text to numbers. The solutions you have seen prior to yours do what you were trying to do in one fell swoop, without the need for the interim column. Look at the others carefully and you will see what I mean.

Duncan

Thanks (0)
avatar
By AnonymousUser
01st Oct 2003 12:04

Using Office 97.
We are using Office 97 (public sector- sigh!).

The data is coming over from a payroll system and comes as text, e.g. "33" or "33-", wrapped in text quotes. So when you added up the column the result was zero. The same effect as trying to add up a list of names.

Thanks

Paul

Thanks (0)
avatar
By AnonymousUser
01st Oct 2003 07:43

I checked again, Paul and can confirm that using Office XP at least there is no need to multiply the positive value by 1 as you suggest. I'd be interested to see if other versions of Office suggest the problem your formula seems to be correcting.

I am assuming here that the positive numbers are NOT initially input as text in some way: I just typed in a value when I typed a positive number and didn't wrap it in "" or anything like that.

Duncan

Thanks (0)
avatar
By davidgough
01st Oct 2003 17:27

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.

Thanks (0)
avatar
By AnonymousUser
30th Sep 2003 10:51

Tidying up.
That is better than my idea. However you do need to multiple the positive text by 1 to get it into a number. The acid test is to run the cursor over the column to sum it. The revised formula is shown below:

=IF(RIGHT(C4,1)="-",-LEFT(C4,(LEN(C4)-1)),C4*1)


Paul

Thanks (0)
avatar
By AnonymousUser
30th Sep 2003 21:00

Office XP does it
I use Office XP Paul and should have said that. The formula I gave this morning worked in XP without the need for any further modification.

Just in case it was a blip, I'll test it again and if there is a problem or further news I'll let you know.

duncan

Thanks (0)
avatar
By AnonymousUser
30th Sep 2003 08:26

A bit more efficient
Paul, the following is a more efficient version of your own solution since it occupies only one cell and it improves on Lawrence's solution too. By the way, you can consolidate your own formula so that it occupies only one cell; and with a large file that could be important to you.

William's solution works well when a number is in the form xx.xx- but if it's in the form xx.xx then it fails: I think he knew that but didn't spell it out!

Assuming the offending text is in cell C4, enter this formula in cell D4:

=IF(RIGHT(C4,1)="-",-LEFT(C4,(LEN(C4)-1)),C4)

My solution assumes that the right hand character in cell C4 is -. If the right hand character in cell C4 is " then either do a search and replace deletion of it or else the =MID() solution is clearly called for.

I tested my formula in the range C4:C8 with the following values all seems OK when you copy my formula into the range D4:D8

33.00-
45.06-
55.12
66.84
79.47-

Duncan

Thanks (0)
avatar
By davidgough
26th Sep 2003 12:40

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.

Thanks (0)
avatar
By AnonymousUser
26th Sep 2003 15:16

Text to numbers
Thanks for all the thoughts below. Much appreciated. I found a solution that works for negative and positive numbers, so it can be copied down a column. If the text is in C4, then in D4 use the formula =len(c4)to find the length of the string. Then in e4 use the following formula which works for positive and negative values.

=IF(MID(C4,D4,1)="-",LEFT(C4,(D4-1))*-1,C4*1)

Cheers

Paul


Thanks (0)
avatar
By ACDWebb
25th Sep 2003 17:37

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

Thanks (0)