Problems with importing excel file (1 Viewer)

[Jmaddog]

I have an excel file that has numbers stored as negative numbers. How can I convert the number to a positive?

 

[MSAccessRookie]

I have an excel file that has numbers stored as negative numbers. How can I convert the number to a positive?

Read up on the Abs() Function, I think that it will be of use to you to resolve this problem.

 

[ahanmagey]

In the cell that you want the data to be converted write either =(C5-C5)-C5 or =C6-C6-C6

C is the cell where the negetive data is.
TIP; if you want to delete the negetive data after converting then mind to copy > past special > values, the converted data.

 

[MSAccessRookie]

In the cell that you want the data to be converted write either =(C5-C5)-C5 or =C6-C6-C6

C is the cell where the negetive data is.
TIP; if you want to delete the negetive data after converting then mind to copy > past special > values, the converted data.

Seems like a lot of work. Won't =Abs(C5) do the same thing?

 

[ahanmagey]

Seems like a lot of work. Won't =Abs(C5) do the same thing?

Hmm you are right. but thats how much i knew. anyway let him to choose whatever he wants. BTW thanks for ur ans as i have learned something too

 

[Rabbie]

The Abs() function is the best if you want to have all number positive. If you want to flip the sign from negative to positive or vice versa the just multiply the field by -1

 

[MSAccessRookie]

Hmm you are right. but thats how much i knew. anyway let him to choose whatever he wants. BTW thanks for ur ans as i have learned something too

I continue to learn things here almost every day. Recently, I even learned a better way to reply to users that helps them to Learn More

 

[Jmaddog]

Thanks for the responses.

My problem is that I about 1500 records that I need to convert to positive numbers and the numbers are not the same. I am not following the Abs function. Can someone please provide me with more detailed instructions on how to do this function. When I try - I end up changing all the records to the same number.

 

[MSAccessRookie]

Thanks for the responses.

My problem is that I about 1500 records that I need to convert to positive numbers and the numbers are not the same. I am not following the Abs function. Can someone please provide me with more detailed instructions on how to do this function. When I try - I end up changing all the records to the same number.

The syntax is something like

NewValue=Abs(OldValue)

 

[boblarson]

Thanks for the responses.

My problem is that I about 1500 records that I need to convert to positive numbers and the numbers are not the same. I am not following the Abs function. Can someone please provide me with more detailed instructions on how to do this function. When I try - I end up changing all the records to the same number.

If you want to update them all to positive numbers, create an update query and for that one field just use

Abs([YourFieldNameInSquareBrackets])

In the area that says UPDATE

 

[Jmaddog]

Thank you very much - that worked. I appreciate everyone's help on this.

 

[Jmaddog]

Is there away to convert decimal places as 1,000s? My numbers are currently 15.031 (which is 15,031).

 

[MSAccessRookie]

Is there away to convert decimal places as 1,000s? My numbers are currently 15.031 (which is 15,031).

If that is the exact format, then you might be able to use the Replace() command switch the "." to ",".

 

[Jmaddog]

I don't think that will work. Some numbers are 100's (0.125)

 

[MSAccessRookie]

I don't think that will work. Some numbers are 100's (0.125)

Are these actual Numbers, or just Text representations of Numbers?

 

[Jmaddog]

These are actual numbers not text

 

[MSAccessRookie]

These are actual numbers not text

Ignore my previous suggestion regarding replace() as it applies to Text fields. I tried to create a field with the value posted below, and was unable to do so. I am aware that the Format is not US Standard, and that may be my problem.

1.234.567.890 trying to convert to 1,234,567,890

 

[Jmaddog]

the numbers only have one decimal place. ex 4,610.256 (4,610,256)

 

[Jmaddog]

They are actual numbers, the numbers only have one decimal place. ex 4,610.256 (4,610,256)

​

 

[MSAccessRookie]

They are actual numbers, the numbers only have one decimal place. ex 4,610.256 (4,610,256)

​

If they are Numbers, then you should be able to treat them as Numbers.

• Use the cstr() and Format() Functions to convert the Numbers into a String with a leading 0 (if necessary).
• Use the Replace() Function to perform the substitution that you need.