I keep getting a #VALUE error when trying to divide two cells. They are
both numbers with decimals. In the error, I click to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the calculation?

Thanks,
Brett

The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

That isn't working. Also, I'd like to apply the conversion to a column.

Any other suggestions?

Thanks,
Brett

"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

I have also tried the suggestions he
http://support.microsoft.com/kb/822665. They don't work. What gives with
this column?

Thanks,
Brett

"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

http://www.cygen.com/temp/stringcell.xls

You can see in E2 that is doesn't calculate. Any suggestions on how to fix
it?

Thanks,
Brett
"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

Hi Brett

there's a space after the 850.00 (D2) which means that it is not recognised
as a number
one way to fix it is to delete the space .... have you got many to do?

Cheers
JulieD

"Brett" wrote in message
...
http://www.cygen.com/temp/stringcell.xls

You can see in E2 that is doesn't calculate. Any suggestions on how to
fix it?

Thanks,
Brett
"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

Yes, there quite a few in that column. Do you have any suggestions?

Thanks,
Brett
"JulieD" wrote in message
...
Hi Brett

there's a space after the 850.00 (D2) which means that it is not
recognised as a number
one way to fix it is to delete the space .... have you got many to do?

Cheers
JulieD

"Brett" wrote in message
...
http://www.cygen.com/temp/stringcell.xls

You can see in E2 that is doesn't calculate. Any suggestions on how to
fix it?

Thanks,
Brett
"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

Hi Brett

looking again it seemed that it was more than just a space - as the normal
method of getting rid of spaces didn't fix the problems - i had to run a
macro over the column - the code below is from
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
--------
Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
------
to use it, open your workbook, right mouse click on a sheet tab and choose
view code - you'll see your workbooks'name on the left hand side of the
screen, if you can't choose view / project explorerclick on the workbook's
name and choose insert / module then copy & paste the above code into the
white hand side of the screen, then use ALT & F11 to switch back to your
workbooknow select the column to fix up and choose tools / macro / macros -
trim all - runlet us know how you goCheersJulieD
"Brett" wrote in message
...
Yes, there quite a few in that column. Do you have any suggestions?

Thanks,
Brett
"JulieD" wrote in message
...
Hi Brett

there's a space after the 850.00 (D2) which means that it is not
recognised as a number
one way to fix it is to delete the space .... have you got many to do?

Cheers
JulieD

"Brett" wrote in message
...
http://www.cygen.com/temp/stringcell.xls

You can see in E2 that is doesn't calculate. Any suggestions on how to
fix it?

Thanks,
Brett
"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

Julie,

Here's another method that is very quick, easy and works. Copy and paste
the column into Word. Then replace all spaces with nothing (Excel will not
do this). Now paste the column back into Excel. Click OK to warning.
Presto!

Thanks for the help.

Brett

"JulieD" wrote in message
...
Hi Brett

looking again it seemed that it was more than just a space - as the normal
method of getting rid of spaces didn't fix the problems - i had to run a
macro over the column - the code below is from
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
--------
Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
------
to use it, open your workbook, right mouse click on a sheet tab and choose
view code - you'll see your workbooks'name on the left hand side of the
screen, if you can't choose view / project explorerclick on the workbook's
name and choose insert / module then copy & paste the above code into the
white hand side of the screen, then use ALT & F11 to switch back to your
workbooknow select the column to fix up and choose tools / macro /
macros - trim all - runlet us know how you goCheersJulieD
"Brett" wrote in message
...
Yes, there quite a few in that column. Do you have any suggestions?

Thanks,
Brett
"JulieD" wrote in message
...
Hi Brett

there's a space after the 850.00 (D2) which means that it is not
recognised as a number
one way to fix it is to delete the space .... have you got many to do?

Cheers
JulieD

"Brett" wrote in message
...
http://www.cygen.com/temp/stringcell.xls

You can see in E2 that is doesn't calculate. Any suggestions on how to
fix it?

Thanks,
Brett
"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.

Hi Brett

glad you found a solution - the edit / replace in Excel didn't find the
spaces, nor did a substitute function .. so dropping them into word must
have changed whatever they were into spaces ...


Cheers
JulieD

"Brett" wrote in message
...
Julie,

Here's another method that is very quick, easy and works. Copy and paste
the column into Word. Then replace all spaces with nothing (Excel will
not do this). Now paste the column back into Excel. Click OK to warning.
Presto!

Thanks for the help.

Brett

"JulieD" wrote in message
...
Hi Brett

looking again it seemed that it was more than just a space - as the
normal method of getting rid of spaces didn't fix the problems - i had to
run a macro over the column - the code below is from
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
--------
Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
------
to use it, open your workbook, right mouse click on a sheet tab and
choose view code - you'll see your workbooks'name on the left hand side
of the screen, if you can't choose view / project explorerclick on the
workbook's name and choose insert / module then copy & paste the above
code into the white hand side of the screen, then use ALT & F11 to switch
back to your workbooknow select the column to fix up and choose tools /
macro / macros - trim all - runlet us know how you goCheersJulieD
"Brett" wrote in message
...
Yes, there quite a few in that column. Do you have any suggestions?

Thanks,
Brett
"JulieD" wrote in message
...
Hi Brett

there's a space after the 850.00 (D2) which means that it is not
recognised as a number
one way to fix it is to delete the space .... have you got many to do?

Cheers
JulieD

"Brett" wrote in message
...
http://www.cygen.com/temp/stringcell.xls

You can see in E2 that is doesn't calculate. Any suggestions on how
to fix it?

Thanks,
Brett
"Jason Morin" wrote in message
...
The content of the cell with the numerator is text.
Formatting the cell as numeric after the fact does not
change the content - it's still a text string.

Click on the numerator cell and do this:

1. Press <Ctrl<Shift<~.
2. Press F2.
3. Press <Enter.

The string in the numerator cell should now be a number.

HTH
Jason
Atlanta, GA

-----Original Message-----
I keep getting a #VALUE error when trying to divide two
cells. They are
both numbers with decimals. In the error, I click
to "show calculation
steps" and see this:

"850.00 "/29634

How can 850.00 be a string if the cell format type is
numeric with two
decimal places?

How can I put the 850.00 as numeric and perform the
calculation?

Thanks,
Brett


.