format a formula to general (not text)

[Emma Aumack]

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"

 

[Gord Dibben]

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

 

[Barb Reinhardt]

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.

 

[Dave Peterson]

I think I'd change the order slightly:

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

 

[Joe User]

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 -----