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
- OzGrid Business Applications
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 :-
(broken link)
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 ]
Aladin
Posted by Celia on December 28, 2000 5:58 AM
Other answers
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
Re: Other answers
Celia: Using just =SUBSTITUTE(A1,"-",)*-1 in B1 and copying down wouldn't work.
Aladin
Posted by Aladin Akyurek on December 28, 2000 6:47 AM
Re: Other answers
Celia: Using just =SUBSTITUTE(A1,"-",)*-1 in B1 and copying down wouldn't work.
Aladin
Posted by Celia on December 28, 2000 3:13 PM
Re: Other answers
It has to be incorporated into an IF statement like Dave's :-
IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-",)*-1 ,A1)
Celia
