Difficult text to number conversion

[Guest]

Hi,

I extract data from an application (SAP) and all my number are formatted as
text. They only way I found so far to convert them is to click on the yellow
diamond and then chose convert to number from the menu. I try the macro
recorder to do the same and nothing is appearing beside my selection. I tried
copy/paste format from a cell in which I enter "1" and it do not convert the
text. I also tried to change the format of the cell manually without success.
The error message from the yellow diamond is (translate from french, so it
could be different) " The number in this cell is formatted as text or is
precede by an apostrophe".

I am still new with programming ( mainly use the macro recorder so far to
learn from it ) and I cannot figure out how to do the trick.

Thanks!

 

[Niek Otten]

Hi Mathieu,

Format an empty cell as Number
Edit>Copy
Select your "numbers"
Edit>Paste special, check Add

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I extract data from an application (SAP) and all my number are formatted as
| text. They only way I found so far to convert them is to click on the yellow
| diamond and then chose convert to number from the menu. I try the macro
| recorder to do the same and nothing is appearing beside my selection. I tried
| copy/paste format from a cell in which I enter "1" and it do not convert the
| text. I also tried to change the format of the cell manually without success.
| The error message from the yellow diamond is (translate from french, so it
| could be different) " The number in this cell is formatted as text or is
| precede by an apostrophe".
|
| I am still new with programming ( mainly use the macro recorder so far to
| learn from it ) and I cannot figure out how to do the trick.
|
| Thanks!
| --
| Mathieu

 

[Guest]

Hi,

It is working to some extent. The problemsI got is that all my number are
added. Ex. A1=1 A2= 2 after the copy paste==> A1=2 and A2=4 So I have to do
another transaction to divide everything 2 , then copy and paste value the
results.

Is there any to circumvent that?

Thanks again!

 

[Niek Otten]

I think you copied the "numbers" range, not the empty cell. So you added each item to itself. You should have added zero (from the
copied empty cell)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi,
|
| It is working to some extent. The problemsI got is that all my number are
| added. Ex. A1=1 A2= 2 after the copy paste==> A1=2 and A2=4 So I have to do
| another transaction to divide everything 2 , then copy and paste value the
| results.
|
| Is there any to circumvent that?
|
| Thanks again!
|
|
| --
| Mathieu
|
|
| "Niek Otten" wrote:
|
| > Hi Mathieu,
| >
| > Format an empty cell as Number
| > Edit>Copy
| > Select your "numbers"
| > Edit>Paste special, check Add
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Hi,
| > |
| > | I extract data from an application (SAP) and all my number are formatted as
| > | text. They only way I found so far to convert them is to click on the yellow
| > | diamond and then chose convert to number from the menu. I try the macro
| > | recorder to do the same and nothing is appearing beside my selection. I tried
| > | copy/paste format from a cell in which I enter "1" and it do not convert the
| > | text. I also tried to change the format of the cell manually without success.
| > | The error message from the yellow diamond is (translate from french, so it
| > | could be different) " The number in this cell is formatted as text or is
| > | precede by an apostrophe".
| > |
| > | I am still new with programming ( mainly use the macro recorder so far to
| > | learn from it ) and I cannot figure out how to do the trick.
| > |
| > | Thanks!
| > | --
| > | Mathieu
| >
| >
| >

 

[Guest]

Hi,

A great thanks to you...I don't understand why I didn't think about that
mistake. Experience is coming slow!

Thanks again!