MS Excel help needed

[FastEddy]

I have a column of numbers with 2 decimal places. I need to change the last 2 numbers after the decimal to .99

All is well using search and replace ( . ) to .99 EXCEPT the numbers that end in (.00) Excel doesn't give (.00) anything after the original number. 999.00 is 999 even though it shows up as 999.00.

If I search for (.00) I get nothing found. Search for (.) and I get everything except .00 and searching for nothing to replace with (.99) doesn't work.

Any ideas or tricks? I have around 6000 numbers to change and doing it by hand isn't an option.

Win 2000 Excel XP 2003.

-Ed

 

[olds97_lss]

Highlight the column, right click, format cells, under the number tab, choose custom.

In the type field, put in 000000.99

This should do what you want. I think I understand what your asking for, but i"m not sure.

To test it, I typed in the following in a column:
1.2
202.21
0.02
2

I then highlighted the column and did what I said above, now it has this in it:
00001.99
00202.99
00000.99

The only think I noticed is that it will round the numbers up on you if what's after the "." is greater than .49
It will round the number in front of the "." up by 1.

Example:
1.49 will be converted to 1.99
1.50 will be converted to 2.99

 

[HumboldtBlazer]

I have a column of numbers with 2 decimal places. I need to change the last 2 numbers after the decimal to .99

All is well using search and replace ( . ) to .99 EXCEPT the numbers that end in (.00) Excel doesn't give (.00) anything after the original number. 999.00 is 999 even though it shows up as 999.00.

If I search for (.00) I get nothing found. Search for (.) and I get everything except .00 and searching for nothing to replace with (.99) doesn't work.

Any ideas or tricks? I have around 6000 numbers to change and doing it by hand isn't an option.

Win 2000 Excel XP 2003.

-Ed

what is my phone not working?

 

[FastEddy]

Getting closer however rounding up isn't acceptable. Errrr...

I got it now,
Changed all the .99 to .00 then did the special format.
At that point nothing was .99 so they didn't round up.
You would think that Excel would recognize a .00 when formatted as $ but it doesn't.

Thanks for putting me on the right path.
-Ed

 

[olds97_lss]

Hey humbolt... you know a formula to do it?

I don't use excel much... just enough to do dumb stuff when I can't get an sql to do it.

 

[vbgagnon]

well the starting with .99 isn't going to be an option, but why odn't you format it all to .00, once that is done format it again to .99 and you problem should be fixed.

 

[olds97_lss]

Actually, I figured out a way, but you will need to have the desired formula in a different column and have it use the column with the crap number in it as the variable.

Anyway, use this:
=CONCATENATE(TRUNC(C1,0),".99")

C1 is the original field. The TRUNC command gets rid of decimal points when you use the ,0 in it. Then you just tag on the .99 with the concatenate command.

Stupid excel!

 

[FastEddy]

So many ways to skin a cat except the easy way.
Using the path that olds gave me I did what vbg posted (Before he posted)
Just think having to go through all this just because Search and Replace SUCKS.

Thanks guys.

HB,
You have a phone? hehe