Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: convert text to number

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



Re: convert text to number

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


Re: convert text to number

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


Other answers

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


Re: Other answers

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.

Aladin


Re: Other answers

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

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

Aladin


Re: Other answers

Posted by Celia on December 28, 2000 3:13 PM

It has to be incorporated into an IF statement like Dave's :-
IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-",)*-1 ,A1)

Celia


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.