Convert to number problem

[Rob]

Hi,

I have pasted some data into a spreadsheet from another accounting
application (MYOB) and have trouble having Excel see the amounts as numbers.
It seems that even if the cells are formatted as a Numbers or Currency,
etc., a simple formula like =SUM(G7:G13) does not see them as numbers.

They all have a small green triangle in top left of cell, and when I select
a cell and select the option "Convert to Number" it will only then be seen
as a number.

If I then try to use the format painter to format all the other cells like
it, the format painter does nothing.

Can someone please tell me what's going on here and a solution, if any, as
the workbook will be used often to import new data.

 

[RichardSchollar]

Hi Rob

You may be able to convert an entire column in one go by selecting it
(the whole column) and going Data>TextToColumns and click Finish.

Hope this helps!

Richard

 

[Guest]

try putting 1 in a blank cell then copy this, select the cells with the
greed tab and edit>paste special and select multiply. Delete the cell with
the 1 in it.

--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Lori]

Select a cell containing a green triangle and press ctrl+A to select
all cells, then choose convert to number, to apply to all cells on the
sheet.

 

[Rob]

Thanks Richard,

I finally worked out how to fix the complete column in one hit, but my Qs
still are,

1. why does the cell format show the cell is formatted as number when it's
not!

and,

2. why didn't the format painter work?

Rob

 

[Dave Peterson]

#1. Changing the format of the cell doesn't change the underlying value. But
the next time you make a change to that value, excel will know that you want it
general (or number or...)

#2. The format painter did its job fine. But its job is not changing
values--just changing format.

And as an aside, if you select the all the cells with that triangle warning
message, you can convert them all in one fell swoop by just chooing convert to
number. (The active cell has to have this triangle warning, though.)

 

[Rob]

Thanks Dave! Very helpful as always!!

I'm still confused why the values in the cells were behaving as text when
the cell format was actually Currency at the time I imported the data.

Rob

 

[Dave Peterson]

I've had this happen to me...

I import a file. One field is imported as text. Then I insert a column to its
right. That inserted column is formatted as text. Excel figures that it should
use the adjacent column to get the format.

But I'm not sure how you could import a file and specify Currency--are you using
the text to columns wizard? There's General, Date, Text and skip, right?

 

[Rob]

Dave,

I created the workbook with the correct formatting as I wanted it. Then I
created a macro which copied columns from an exported excel file which was
generated from an accounting software program (called MYOB). The macro
pastes the data via:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I noticed that after the values had been pasted, that the formulas adding
some of the values were all blank.

I think the problem originates with the exported file from MYOB which I've
just noticed exports the values as text. Which means I'm copying Text to be
pasted into my workbook!
I've done this similar process in a different workbook and have not struck
this problem.

Is there a simple procedure I can add to the macro whereby they are pasted
as Currency, or simply Number?
Failing that, is there some code I can add to apply the Currency or Number
format to that column (2 dec places).

Rob

 

[Dave Peterson]

Maybe you can just assign the values instead of copy|pasting. In my little
test, it worked fine.

Dim RngToCopy as range
dim DestCell as range

with someworksheetfrommyob
set rngtocopy = .range(somerangehere)
end with

set destcell = someotherworkbook.worksheets("someothersheet").range("a1")

with rngtocopy
destcell.resize(.rows.count,.columns.count).value = .value
end with

========
If that doesn't work for you, you can select an empty cell and copy it. Then
select the range to fix and edit|paste special|Add.

In code:

Dim EmptyCell as range
with worksheets("someworksheet")
set emptycell = .cells.specialcells(xlcelltypelastcell).offset(1,1)
end with

emptycell.copy
somerangetofixhere.pastespecial Paste:=xlPasteValues, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False

Dave,

I created the workbook with the correct formatting as I wanted it. Then I
created a macro which copied columns from an exported excel file which was
generated from an accounting software program (called MYOB). The macro
pastes the data via:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I noticed that after the values had been pasted, that the formulas adding
some of the values were all blank.

I think the problem originates with the exported file from MYOB which I've
just noticed exports the values as text. Which means I'm copying Text to be
pasted into my workbook!
I've done this similar process in a different workbook and have not struck
this problem.

Is there a simple procedure I can add to the macro whereby they are pasted
as Currency, or simply Number?
Failing that, is there some code I can add to apply the Currency or Number
format to that column (2 dec places).

Rob

 

[Rob]

Thanks Dave.

I couldn't get your first procedure to work.
Your second code did the trick though, which was actually better as it meant
less fidling with the code I already had.

Rob