# Convert Negative Number Macro

Jun 12, 2007
I have this report that comes as a text file.

It makes negative numbers look like 1245.35-putting the - sign at the end. I need some way to make these negative numbers look like -1245.35. Only these negative numbes need to be re-formatted.

View 9 Replies
ADVERTISEMENT
Nov 12, 2012

I keep getting the correct answer, but it returns it as negative and I need a positive answer.

View 1 Replies
View Related
Aug 27, 2012

I am looking for a conditional formula that can be applied to a cell so that when I enter a number with a positive value, it:

1) Converts it to its negative equivalent value.

2) Displays in the value in the cell with parentheses around the number to indicate that the value is negative.

I am trying to do this without having to reference another cell to provide the entry or display the outcome.

View 1 Replies
View Related
Aug 1, 2007

I have data that comes from a subsytem that places the negative sign at the right of the number, so it is recognized as text. I can get around this using find and replace and then a second step to multiply that by -1, but is there a formula that can do this for me?

I was trying if(right(A1,1)="-",TBD,A1)

View 4 Replies
View Related
Mar 11, 2009

Is there a simple way to convert a positive number to a negative number ?

Fromto

10(10.00)

56(56.00)

116(116.00)

Additionally, would the same procedure work for time?

So if I had a column listing hours that looks like this:

10:05:00

4:34:00

2:18:06

is there a way to convert this to negative hours? Understandably, there are no such thing as negative hours, but I am trying to illustrate a situation where an internal procedure was completed x amount of hours before the specified start time.

View 6 Replies
View Related
Jan 23, 2008

In an excel column listing monthly payments, I cannot report a negative value . I need to "carry forward" the negative value by automatically adding it to the next positive cell in the column. This process should loop through the column leaving no negative numbers, leaving a zero in any cell where the negative number was originally and balance out to be the same amount as the sum of the original listing of monthly payments.

View 4 Replies
View Related
Jun 27, 2014

I wish to convert a negative figure Ie (-1,500) to positive. How do i go about it?

View 2 Replies
View Related
Jan 8, 2008

I have created a Pivot table from a source data file that includes a document status column ('Type I Or C') (I for Invoice & C for Credit) and an Invoice / Credit value column (Invoiced Price). However the values for both invoices and credits are positive so when summed distort the total values.

I am attempting to create an additional column within the Pivot table to convert the credit note values to negative by inserting a calculated field under the formula tab, i.e.

=IF('Type I Or C'="C",-'Invoiced Price','Invoiced Price')

The above formula works fine in a standard worksheet but it is not reckonsing the "C" within the pivot table. If I manually change the value to numeric (i.e. 1 for Invoices and 2 for credits) it works fine, i.e =IF('Type I Or C'="2",-'Invoiced Price','Invoiced Price')

so it appears to be an alpha issue?

View 5 Replies
View Related
Nov 19, 2007

Have the following numeric values:

-3.46

103

0

6.2

I would like to convert (not format) them to

the following 14-byte text values:

-0000000000346

+0000000010300

+0000000000000

+0000000000620

View 9 Replies
View Related
May 16, 2014

I have a very large exported data base to excel. See the Attachment. I have columns on the left with text that needs to stay as text, and rows at the top with dates. These are my filters to sort and view my data. The current problem I am having is some of the quantities in the bulk of my spreadsheet are coming in as text that need to be converted to numbers (that stupid green triangle in the upper left hand corner). I already have developed a macro to format, sort, extract, and place the data so it's more manageable to look through. I need to get rid of this convert to number error before running this macro.

I am looking to develop a macro that will find each of these "convert text to number" errors and convert the text to number. So far I have been unable to find a starting point.

The sample attached is only 1% of the actual size of the spreadsheet, and my exported data base is variable in size.

The only solution I have at the moment is to go to the bottom of my spreadsheet, find the last error, and highlight everything above and done one convert to number.

Sample.xlsx

View 3 Replies
View Related
Mar 27, 2014

This is what I have so far.

Code:

Range("L:L").Select

With Selection

Selection.NumberFormat = "General"

.Value = .Value =

.NumberFormat = "0.0"

[Code]...

The .NumberFormat = "0.0" is newly added and doesn't work.

View 9 Replies
View Related
Aug 9, 2006

I have a range of numbers stored as text. I need to convert them to long numbers using vba. I tried typing a simple number = clng(a2), but that didnt work. Can someone help me with A) the right formula and B) the proper end(xlUp) format.

View 9 Replies
View Related
Feb 16, 2008

I use currency data pasted from legacy program that pastes dollar values followed by a C or D to represent debit and credits, ie -- 1,000,000.00C or 1,000,000.00D.

Is there some way to remove the letter and correctly format the value to a negative or positive number on a whole worksheet or column?

Currently I am doing this manually one cell at a time.

View 6 Replies
View Related
Mar 7, 2008

I have this data source file that comes from a back office program, and basically what i want it to do is to turn the number negative in column C when the header of that column in column A is WITHDR and leave it positive when it is CONTR. What i was thinking was that it could go to column A from column C and go up each cell until the cell is not a date and multiply the column C number by -1 depending the column A header equals WITHDR and do that for every cell in column C, but I dont know how to proceed, im new at VBA.

View 8 Replies
View Related
Jul 1, 2008

I have a column of numbers such as

1001150

1001124

2224445

I need add a period in the following locations

10011.50

10011.24

22244.45

I figured this out using a format rule of

#.##

I then need to make the numbers negative so I did

-#.##

but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.

View 4 Replies
View Related
Aug 21, 2014

I go negative in one column I want it to show up in the next column as a positive number because when I add my income and subtract my bills and the negative from last month to get the what cash I'll have left it screws up because negative subtract a negative is a positive. So how do i get the sum of say B9 to from -$5.00 to +$5.00 into C4 so when i subtract C4 From my income it wont add to it.

View 1 Replies
View Related
Mar 9, 2013

I would like to add to this formula.... if the result of "$L$6-Q15", from below, is 0 or a negative number, then the desired result would be 0 for this formula.

Possibly wrapping IF AND somewhere in this...

=IF((O15+(K16*$L$5))>=$L$6,$L$6-Q15,K16*$L$5)

View 2 Replies
View Related
Aug 15, 2007

This may sound simple, but is not (it might be easy for you hopefully)...... I have a string of numbers and the first few numbers can be either postive, negative or zero....Later on, the numbers all become negative......

I need to find the the cell which contains the first negative number in the consequtive negative numbers.....The difficult part is the numbers before the consequtive negative numbers can be anything......

View 9 Replies
View Related
Jan 2, 2008

is there a way to format a cell that will make the number a negative number regardless of what number I type; whether I type a positive or negative.

What i'm trying to accomplish is enter a number and the number will be negative. I don't want to have to type a negative in front of the number.

View 9 Replies
View Related
Oct 27, 2009

Is there a way to a formula that would show the following? A plus B = C but if A is below 0, stop the value at 0 then add column B:

Columns:

A B C

-2 5 5

1 5 6

0 5 5

View 2 Replies
View Related
Jan 28, 2010

How can I make excel to recongnize this: 123456- as a negative number?

View 7 Replies
View Related
Jun 11, 2014

How would I write an IF statement that would tell me if a number in one cell is more negative than another?

For Example,

Cell L23 has a number of -10mm

Cell M23 has -20mm

How would i write an if statement in another cell saying that if cell 23 goes beyond -20mm "warning".

I keep getting an error saying #NAME?

View 9 Replies
View Related
Jul 6, 2007

How do I create a formula that tells me if a negative number appears in a column.

for example, the following numbers are in cell A1:A5

10

15

-1

12

8

I need a formula in B2 that tells me if A1:A5 contains a negative number.

View 3 Replies
View Related
Aug 14, 2008

is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"

Below is a small sample of data, real data is around 100 rows

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

View 9 Replies
View Related
Apr 3, 2014

Here it is i needed to get the total of all negative and positive value.

View 2 Replies
View Related
Dec 11, 2008

I want to subtract column B from column A and put the result in column C. If the result is a negative I want a formula to turn it to 0.

View 4 Replies
View Related
Apr 7, 2009

I have numbers in range B1 to B100. How do I search for the first negative number (not the smallest negative number) within the range? Then, retrieve the Date indicated along A1 to A100?

View 8 Replies
View Related
Jan 3, 2012

I need formula for if cell value = negative number e.g.(-2202) change cell value to 0

View 5 Replies
View Related
Oct 25, 2007

In one column, I have a formula that will produce a positive or negative number. If the number is negative, I want it to be automatically transfered to the adjacent cell/column. I don't want a positive number transfered though.

View 9 Replies
View Related
Jun 2, 2008

My bank sends me my statement data as: Description, Date, Amount

...where the amount is text as a number followed by either a plus or minus.

Does anybody know how quickly convert the column of data into proper numbers?

I've been playing around with LEN, RIGHT and SEARCH, but not getting very far because I don't know how to extract the text to the left given the numbers vary in the number of digits...

View 9 Replies
View Related