format a formula to general (not text)

I am importing a text (.csv) file into excel and formatting it via a Macro.  
in one column I have to insert a formula but when I do so, the formula is 
showing, i.e. "=TRIM(Z4)".  I have tried to format to "general" but it 
doesn't work unless I click inside the cell and press enter.  How do I apply 
the "General" formatting without have to manually click inside the cell?  I 
want to do it in my macro.

Here is my code:

    ActiveCell.Formula = "=TRIM(Z4)"
    Selection.NumberFormat = "General"


-- 
www.bardpv.com
Tempe, Arizona
1
Utf
3/16/2010 9:58:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1431 Views

Similar Articles

[PageSpeed] 39

The cells are Text formatted.

Simply changing the format will not do the trick as you have found.

Try this construct

With ActiveCell
        .Formula = "=TRIM(Z4)"
        .NumberFormat = "General"
        .Value = .Value  'same as F2>Enter
End With


Gord Dibben  MS Excel MVP

On Tue, 16 Mar 2010 14:58:01 -0700, Emma Aumack <emma.aumack@crbard.com>
wrote:

>I am importing a text (.csv) file into excel and formatting it via a Macro.  
>in one column I have to insert a formula but when I do so, the formula is 
>showing, i.e. "=TRIM(Z4)".  I have tried to format to "general" but it 
>doesn't work unless I click inside the cell and press enter.  How do I apply 
>the "General" formatting without have to manually click inside the cell?  I 
>want to do it in my macro.
>
>Here is my code:
>
>    ActiveCell.Formula = "=TRIM(Z4)"
>    Selection.NumberFormat = "General"

0
Gord
3/16/2010 11:04:48 PM
Try this

With ActiveCell
    .NumberFormat = "General"
    .Formula = "=TRIM(Z4)"
End With

Your cell is probably preformatted as text, so when you enter the formula, 
it remains that way.   
-- 
HTH,

Barb Reinhardt



"Emma Aumack" wrote:

> I am importing a text (.csv) file into excel and formatting it via a Macro.  
> in one column I have to insert a formula but when I do so, the formula is 
> showing, i.e. "=TRIM(Z4)".  I have tried to format to "general" but it 
> doesn't work unless I click inside the cell and press enter.  How do I apply 
> the "General" formatting without have to manually click inside the cell?  I 
> want to do it in my macro.
> 
> Here is my code:
> 
>     ActiveCell.Formula = "=TRIM(Z4)"
>     Selection.NumberFormat = "General"
> 
> 
> -- 
> www.bardpv.com
> Tempe, Arizona
0
Utf
3/16/2010 11:22:01 PM
I think I'd change the order slightly:

With ActiveCell
    .NumberFormat = "General"
    .Formula = "=TRIM(Z4)"        
    .Value = .Value  'same as F2>Enter
End With


Gord Dibben wrote:
> 
> The cells are Text formatted.
> 
> Simply changing the format will not do the trick as you have found.
> 
> Try this construct
> 
> With ActiveCell
>         .Formula = "=TRIM(Z4)"
>         .NumberFormat = "General"
>         .Value = .Value  'same as F2>Enter
> End With
> 
> Gord Dibben  MS Excel MVP
> 
> On Tue, 16 Mar 2010 14:58:01 -0700, Emma Aumack <emma.aumack@crbard.com>
> wrote:
> 
> >I am importing a text (.csv) file into excel and formatting it via a Macro.
> >in one column I have to insert a formula but when I do so, the formula is
> >showing, i.e. "=TRIM(Z4)".  I have tried to format to "general" but it
> >doesn't work unless I click inside the cell and press enter.  How do I apply
> >the "General" formatting without have to manually click inside the cell?  I
> >want to do it in my macro.
> >
> >Here is my code:
> >
> >    ActiveCell.Formula = "=TRIM(Z4)"
> >    Selection.NumberFormat = "General"

-- 

Dave Peterson
0
Dave
3/16/2010 11:59:42 PM
"Gord Dibben" <gorddibbATshawDOTca> wrote in message >
> Try this construct
> With ActiveCell
>        .Formula = "=TRIM(Z4)"
>        .NumberFormat = "General"

I think it is good to get in the habit of setting .NumberFormat before 
setting .Formula.

That avoids problems like the one which Barb presumes is the root cause of 
Emma's problem.

Also, consider the difference between:

..Value = "12345678901234567890"
..NumberFormat = "@"

and

..NumberFormat = "@"
..Value = "12345678901234567890"

I suspect the second form is what most people want.

I think the result of the first form is very strange, to say the least. 
Assuming the cell format is General and the column width is the default to 
begin with, the first form results in a number displayed as General 
(TYPE(...) returns 1), but it is left-justified.  If the cell is 
subsequently re-evaluated (e.g. press F2, then Enter), the result is text 
(TYPE(...) returns 2); but the text is the first 15 significant digits 
followed by zeros -- exactly what we see in the Formula Bar before 
re-evaluating the cell.


>        .Value = .Value  'same as F2>Enter

I don't think so.

The statement above replaces the formula with the result of the formula; 
that is, it replaces the formula with a constant value.  In contrast, 
pressing F2, then Enter would simply re-evaluate the formula.  But the 
formula will still be left in the cell.


----- original message -----

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:mr20q5ttfkuebe4e7pt3ufa864rbdtftqh@4ax.com...
> The cells are Text formatted.
>
> Simply changing the format will not do the trick as you have found.
>
> Try this construct
>
> With ActiveCell
>        .Formula = "=TRIM(Z4)"
>        .NumberFormat = "General"
>        .Value = .Value  'same as F2>Enter
> End With
>
>
> Gord Dibben  MS Excel MVP
>
> On Tue, 16 Mar 2010 14:58:01 -0700, Emma Aumack <emma.aumack@crbard.com>
> wrote:
>
>>I am importing a text (.csv) file into excel and formatting it via a 
>>Macro.
>>in one column I have to insert a formula but when I do so, the formula is
>>showing, i.e. "=TRIM(Z4)".  I have tried to format to "general" but it
>>doesn't work unless I click inside the cell and press enter.  How do I 
>>apply
>>the "General" formatting without have to manually click inside the cell? 
>>I
>>want to do it in my macro.
>>
>>Here is my code:
>>
>>    ActiveCell.Formula = "=TRIM(Z4)"
>>    Selection.NumberFormat = "General"
> 

0
Joe
3/17/2010 12:20:40 AM
Reply:

Similar Artilces:

How do I extract text content from an exported .rwz file
Outlook 2003, Office Pro - When you export Rules and create a .rwz file, the file is not a pure text file as it seems to be loaded with null chars. I would like to extract the long list of spam words used in a particular rule. Is there a way or a utility which will remove all the nulls from the file so I may edit it to get the contents I want ? The alternative storage location is not any easier - trying to get the same info, as it is similarly stored, out of the registry. :( -Ric Not possible. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and R...

background image formatting
Hi All, How can I make a background image stretched instead of tiled? I am using a default picture in publisher of clouds and they of course don't look right being tiled. Thanks a lot David Resize the image? --- If you're asking about web bg's, you can but the result is u g l y -- Rob Giordano Microsoft MVP - FrontPage "David" <David@discussions.microsoft.com> wrote in message news:9E6E0F22-9511-4EEC-963B-0B359D511FA2@microsoft.com... | Hi All, | How can I make a background image stretched instead of tiled? | I am using a default picture in publisher ...

change control source or value of text box
I have a report with GroupHeader0 and GroupHeader1. The data source of the report is a query. In the GroupHeader0 I have the Purchase Order Header info which includes the Purchase Order due date. In the GroupHeader1 I have the Purchase Order line info which includes the line due date. What I need is if the line due date is Null or blank to default to the PO header due date. How can I assign the header due date to the line due date. Can I change the control source of the text box in VB programming. -- MNJoe Put the following expression in the first blank field in your query: ...

saving format
i have a spreadsheet that i backup & copy to a new folder but the cell formulas are replaced with the cell values. how do i keep the formula format. thanks Save two copies??? One with the formulas and one after the formulas have been converted to values. bob wrote: > > i have a spreadsheet that i backup & copy to a new folder but the cell > formulas are replaced with the cell values. how do i keep the formula > format. > > thanks -- Dave Peterson ec35720@msn.com Or are you saying that when you make the backup copy the formulas are somehow changed to values...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

paste-linking annotating text boxes
I'm trying to insert an Excel chart into a PowerPoint presentation and link it dynamically. The data link is not an issue but I cannot find a practical way to paste the text boxes that were added to the Excel chart by way of annotation. Selecting and pasting the chart itself and the textboxes as if they were separate objects results in a distorted PowerPoint layout. Is my only choice to add the text boxes anew in PowerPoint? Select the chart prior to adding the textbox. The textbox then will be part of the chart, rather than just another drawing object floating above the worksh...

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Conditional Formating Furmula
I have to enter a 14 digit licence number in a field this is a mix of numbers and leters. can some one please give me a formula that i can use in conditional formating tol show if there are to few or to many digits. (Using excel 2003) Thaks in advance. CF/ Formula is/ =LEN(A1)<14 Format as desired for too few CF/ Formula is/ =LEN(A1)>14 Format as desired for too many Rather than using CF you could, of course, use Data validation to require LEN(A1) to be 14. -- David Biddulph "jockj215" <jockj215@discussions.microsoft.com> wrote in message news:A24...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Conditional Formatting
Hi All, I need help on conditional Formatting. I have a column of data with future DATE, such as 2 Jan 09, 4 Des 09, etc I would like to assign automatically different colors to those data that match these condition: If (TODAY's date < Data's date) And more than 30 days, COLOUR is Orange If (TODAY's date < Data's date) And more than 60 days, COLOUR is Yellow If (TODAY's date < Data's date) And more than 90 days, COLOUR is Green If (TODAY's date >= Data's date) And more than 30 days, COLOUR is Red I would like to gave it AUTO...

Blurry text in Bold
Bold characters in excel cells are blurred (reduced clarity) regular text appears fine. Please advise on remedy I understand that Word 2003 has similar issues on regular font. (deactivate ClearType was solution for word) I cannot find any info regarding Excel 2003 in knowledge base ...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

weird formatting
Hi all, Anyone ever seen this before? This was what happened to a mac excel spreadsheet the user doesnt know how she did this but i wondered if anyone might recognize this current format. In hopes we can change it back. Formatting is below. thx for any help ... Ed 2oC//9yIAGAf0L//2tC//9qQAAg= </data> <data> AG0AFgAAPz8/Pz8/Pz8BcF9AAAAA JQUubXBlZwAADHZpZGVvL3gtbXBl Zw9NUEVHIG1lZGlhIGZpbGVtc2Z0 AAAAMr//AAIRUXVpY2tUaW1lIFBs dWdpbgDagL//3IgAYB/Qv//a0L// 2pAACA== </data> <data> AGwAFgAAPz8/Pz8/Pz8B...

Text fields in report writer
I have a modified report dictionary, when I go into a report layout and enter a text field, when I tab off the field what I wrote is converted to something totally different. Has anyone seen this before? Reports that I have been using for months suddenly the text fields print something totally different. ????? Any suggestions would be helpful. Thank you! Hi J. We use a shared dictionary located on our server, and I find text fields do not "travel" well - ie. I often lose info if I export a report out of the dictionary to work on it. What I do is make a copy of the enti...

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...