# convert text to number

Posted by Andonny on December 28, 2000 2:11 AM

Hi,
I have culumn A filled with numbers. But there is the odd cell with a text like 5- which should really be a number -5 that I can sum it. How can I convert this dynamically to a negative number. There could be many or only a few like this.
Example:
.......A
1..... 4
2......7
3......2-
4......10
5......5-
6......34
7......9
8......27-
9......31
10.....2-
11.....20

Many thanks for your kind help
Andonny

Posted by Dave on December 28, 2000 2:22 AM

Hi Andonny

Supposing your numbers are in column A, put this formula in B1:
IF(RIGHT(A1,1)="-",VALUE("-"&LEFT(A1,LEN(A1)-1)),A1)

Then PasteSpecial over the top of them as Values to remove the formulas.

Dave

Posted by Celia on December 28, 2000 2:44 AM

If you have to this a lot, you might want to use a macro. There is one available entitled DASMASHER_VBA.xls at :-

Celia

Posted by Aladin Akyurek on December 28, 2000 4:00 AM

If "dynamically" doesn't mean some VB code per se, you might try the following:

Assuming that the first entry/value is in A1, put in B1:

=IF(ISNUMBER(A1),A1,IF(ISNUMBER(VALUE(CONCATENATE("-",SUBSTITUTE(A1,"-","")))),VALUE(CONCATENATE("-",SUBSTITUTE(A1,"-",""))),"")) [ copy this down as far as needed ]

and in (e.g.,) C1:

=SUM(B:B) [ if col B contains the converted values only ]

Posted by Celia on December 28, 2000 5:58 AM

There are some neat answers to this question posted on another Excel forum :-

Formula :-
=SUBSTITUTE(A1,"-",)*-1

Macro from:- http://www.mindspring.com/~tflynn/excelvba.html

Sub MoveMinus()
Dim mycell As Range
For Each mycell In Selection
If Right(mycell, 1) = "-" Then
mycell.Value = mycell.Value * 1
End If
Next
End Sub

Celia

Posted by Aladin Akyurek on December 28, 2000 6:18 AM

Celia: Using just =SUBSTITUTE(A1,"-",)*-1 in B1 and copying down wouldn't work.

Posted by Aladin Akyurek on December 28, 2000 6:47 AM