HELP ME PLEASE ---- COMPUTERS ARE STARTING TO FLY!

[kathleen]

I have a web page (I created it in the so called compatiable product of FrontPage ) that's using an Office Component to deliver a Office Spreadsheet. When the user views the page they see an edit with Excel option.

My problem is that it is delivering the Spreadsheet in text format. One of the columns is formatted in Currency. I can strip the $ out and the commas out but Excel is still treating it as text. There is a space at the end of the field that I can not eliminate. Any clues?

 

[Brian from Maui]

I have a web page (I created it in the so called compatiable product of FrontPage ) that's using an Office Component to deliver a Office Spreadsheet. When the user views the page they see an edit with Excel option.

My problem is that it is delivering the Spreadsheet in text format. One of the columns is formatted in Currency. I can strip the $ out and the commas out but Excel is still treating it as text. There is a space at the end of the field that I can not eliminate. Any clues?

With cell still formatted as currency and text, try

=--TEXT(A1,0)

 

[just_jon]

Have you tried at http://www.theofficeexperts.com/frontpage.htm -- might be more up their alley. [See the Free Help and send our regards.]

EDIT -- She hasn't yet teed off Brian -- I was thinking this might be more a Frontpage problem that an Excel patch, but you might be right. BTW, coverage here not til 6 CT.

 

[kathleen]

Thanks but now i get a #VALUE on it. Did I mention that the number should be 7 characters long without # and comma but when I perform a length function it returns as 8.

BTW what does the -- behind the = sign mean?

 

[just_jon]

The -- bit coerces a numeri from a text value; wonder if you've got the {shudder} dread CHAR(160) in there -- there's a macro called TrimAll here somewhere that handles that and spaces as well. Will find it if time.

 

[kathleen]

I am sorry, didn't mean to tee anybody off. Nothing but the highest respect for you all. Didnt know about the other site. Will check there as well. Thanks for the tip.

 

[Royal Presence]

What version of FP are you using?

 

[just_jon]

Here it is -- http://www.mrexcel.com/board2/viewtopic.php?t=71961&highlight=trimall+macro
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

 

[kathleen]

Using FP 2002. Thanks.

 

[just_jon]

I am sorry, didn't mean to tee anybody off. Nothing but the highest respect for you all. Didnt know about the other site. Will check there as well. Thanks for the tip.

I AM SO SORRY -- I was making an aside to Brian about the lady golfer out in Hawaii! You're just fine -- I'M the one who needs to apologise! Please accept.