Changing pasted cell format to numeric does not work. 1

[jlockley]

Cells were cut and paste from the Berlin Sparkasse web site. Using the format option they were then reformatted as numeric general. A simple formula (=A1*$C$1 , where the value of C1 was the conversion factor for Euros to dollars) would not function. The suggested solutions, using the error tab for changing the values, using paste special (which will not work with unicode or possibly text) and others did not work. After several hours I resorted to typing in all the values and found that the formulas functioned, proving that the issue was with the non numeric format. A macro copied from an obliging was also non functional. I thought it was the macro, but it was certainly the content of the cell.
Does anyone know a workaround or a solution? I tried pasting it to word and saving it as rtf then returning the cells to Outlook so that they could be reformatted using the error method, but this did not work either. Microsoft's solutions were useless.

 

[SkipVought]

Hi,

No matter how hard I try, my mind cannot picture what is specifically in the Excel sheet to which you are referring.

You copy (you can't really CUT from a web site) SOMETHING from a web site and paste it into your sheet. Then you changed the number format of that range to General.

Actually that resulted in NO CHANGE TO THE DATA IN THAT RANGE. I don't know what you expected to happen, but merely changing the Number Format changes nothing in the underlying data.

So please post a sample of the data you pasted and explain what you wanted to happen when you changed the Number Format to General.

 

[jlockley]

Here is the original data. The Eur designation was removed. To be more precise it was copied from the printout sheet sheet provided by the bank. I assumed the format was HTML, not PDF, as it was not downloaded. German online banking is a little weird. There is no .csv or other importable option.

-4.00 EUR

-192.36 EUR

-24.89 EUR
-33.00 EUR


-0.60 EUR

-4.00 EUR

-192.36 EUR


-24.89 EUR


-33.00 EUR


-4.00 EUR

-192.36 EUR


-24.89 EUR


-33.00 EUR


-0.60 EUR

-4.00 EUR

-192.36 EUR


-24.89 EUR


-62.90 EUR


-15.05 EUR


-200.00 EUR


-18.00 EUR


-9.47 EUR


-33.92 EUR


-4.78 EUR


-33.00 EUR


-4.00 EUR

-32.50 EUR


-14.95 EUR


-24.00 EUR


-200.00 EUR


-62.95 EUR


-192.36 EUR


-10.21 EUR


-19.99 EUR


-1.743.82 EUR


-131.89 EUR


-200.00 EUR


-24.89 EUR


-207.50 EUR


2.736.14 EUR




-33.00 EUR


-4.00 EUR

-192.36 EUR


-24.89 EUR


-33.00 EUR


-0.60 EUR

-4.00 EUR

-192.36 EUR


-24.89 EUR


-33.00 EUR


-4.00 EUR

-192.36 EUR


-24.89 EUR


-10.71 EUR


-51.96 EUR


-6.70 EUR


-300.00 EUR


-100.00 EUR


-33.00 EUR


-4.00 EUR

-21.72 EUR


-300.00 EUR


-192.36 EUR


-300.00 EUR


-24.89 EUR


-15.00 EUR


-449.00 EUR


-40.40 EUR


-500.00 EUR


-250.00 EUR


3.595.00 EUR




-33.00 EUR


-0.60 EUR

-4.00 EUR

-192.36 EUR


-24.89 EUR


-33.00 EUR


-33.00 EUR


-4.00 EUR

-192.36 EUR


-8.50 EUR


-8.50 EUR


-24.89 EUR


-33.00 EUR


-0.60 EUR

-4.00 EUR

-192.36 EUR


-24.89 EUR


-33.00 EUR


10.00 EU

 

[jlockley]

and thank you.

 

[SkipVought]

Thanks.

So now, prior to deleting or changing ANYTHING, select that column, activate the Data TAB and perform a Text to Columns... using DELIMIT/SPACE. Make the first column General and do not import the second column.

 

[jlockley]

Thanks, but afraid not. Using the same formula (=A1*$C$1 , where C1 is 0.784)The same error shows that the formula attempts to calculate: "-60"*0.784 meaning that the cell contents are still being processed as text of some sort. This time paste special cannot be called up. When Paste special can be called up the interface has two check boxes only, for unicode or text.
Thanks for trying.

 

[SkipVought]

Well it turns out that what appears to be a SPACE is ASCII code 160 (rather than 32)

So I took your example, copied the LAST 4 CHARACTERS in the first cell, pasted that into the Find & Replace tool FIND and replaced all.

The results were that all your numeric characters converted to numbers.

 

[jlockley]

Thank you very much. I will need this again. I am not quite sure what you mean by last 4 characters, however - " Eur"? Also, how does one tell this? Very interesting.

 

[SkipVought]

I don't know what is difficult about the last 4 characters of the text.

"Also how does one tell this?"

Tell WHAT?

If that worked for you AND you'll need to use it again, you can RECORD a macro and SAVE it in your PERSONAL.XLSB workbook so that it would be available for future reference.

 

[Andrzejek]

Wouldn't these be the "last 4 characters"?
[tt]
-4.00[highlight #FCE94F] EUR[/highlight]
-192.36[highlight #FCE94F] EUR[/highlight]
-24.89[highlight #FCE94F] EUR[/highlight]
-33.00[highlight #FCE94F] EUR[/highlight][/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[jlockley]

Yes. I had removed EUR and had those numbers in (addled) mind. As I responded above, just a simple VBA script removing the non breaking space works, too. This improves things immensely.