Back to Forms in Excel VBA archive index

Back to archive home

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

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 | ||||

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

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

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

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

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

Aladin

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

Aladin

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.

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.