Convert to number problem
 

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.

Convert to number problem
 

Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.

Convert to number problem
 

try putting 1 in a blank cell then copy this, select the cells with the
greed tab and editpaste 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)


"Rob" wrote:

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.

Convert to number problem
 

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.

On 14 Mar, 05:24, "Rob" <none wrote:
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.

Convert to number problem
 

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.

Convert to number problem
 

#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 wrote:

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.



--

Dave Peterson

Convert to number problem
 

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" wrote in message
...
#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 wrote:

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.



--

Dave Peterson

Convert to number problem
 

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

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" wrote in message
...
#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 wrote:

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.



--

Dave Peterson

--

Dave Peterson

Convert to number problem
 

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" wrote in message
...
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 wrote:

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" wrote in message
...
#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 wrote:

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.



--

Dave Peterson

--

Dave Peterson

Convert to number problem
 

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").ran ge("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



Rob wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
#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 wrote:

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Convert to number problem
 

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

"Dave Peterson" wrote in message
...
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").ran ge("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



Rob wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
#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 wrote:

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

"RichardSchollar" wrote in message
oups.com...
Hi Rob

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

Hope this helps!

Richard



On 14 Mar, 05:24, "Rob" <none wrote:
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.



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson