Copy formatted cell values to another range as text

[ttt123]

How can I copy a bunch of formatted values to a new range and have them as text values exactly the way they were formatted?

for example suppose my Regional Settings are set to French-Canada(note that French-Canada uses a comma for decimal point instead of a period). Note that I choose French-Canada because when the locale is not set to English-US we can experience different behavior than what we normally do.

Suppose I set the following range values:
Range A1:
Value = 1,23
NumberFormat = "[$€-2] #,##0.00"
Displays As: €1,23

Range A2:
Value=0,05
NumberFormat="0.00%"
Displays As: 5,00%

Things I have tried:
a)
Format B1:B2 as Text, Copy A1:A2, Paste Special Values -> Doesn't work converts it to 1,23 and 0,05

b)
set B1's Formula to: =TEXT(A1,"[$€-2] #,##0.00")
set B2's Formula to: =TEXT(A2,"0.00%")

this works when Regional Settings is set to English-US, but not for other Regional Settings like French-Canada:
French Canada will display € 1,230.00, and 0.05% which is wrong

c)
I can create a module with some VBA code and call it via in-cell functions. This works but I don't want to do this.
Public Function TextVBA(r As Range) As String
TextVBA = r.Text
End Function

d)
I can copy the cells, paste them in Notepad, copy the text from Notepad and then paste in Excel. This also works, but I also don't want to do this.


-------------------------------
I am developing a C# addin and using the Text function over and over again is slow due to COM Interop. Using the clipboard is a no-no since it is a shared system resource.

Is there any other way that I can extract the text from one cell to another? I don't mind getting manual steps since I can figure out how to automate them via code.

 

[BQardi]

Hi ttt123
Instead of:

set B1's Formula to: =TEXT(A1,"[$€-2] #,##0.00")
set B2's Formula to: =TEXT(A2,"0.00%")

Have you tried switching the comma and period around:

set B1's Formula to: =TEXT(A1,"[$€-2] #.##0,00")
set B2's Formula to: =TEXT(A2,"0,00%")

 

[ttt123]

Hi ttt123
Instead of:

set B1's Formula to: =TEXT(A1,"[$€-2] #,##0.00")
set B2's Formula to: =TEXT(A2,"0.00%")

Have you tried switching the comma and period around:

set B1's Formula to: =TEXT(A1,"[$€-2] #.##0,00")
set B2's Formula to: =TEXT(A2,"0,00%")

Hi, thanks for your response, I have tried with both NumberFormat and NumberFormatLocal(its actually =TEXT(A1,"[$€-2] # ##0,00") for NumberFormatLocal with French-Canada as Regional setting). When passing a NumberFormatLocal to the TEXT function, it will give an error saying that there is something wrong with the formula. I think it can only take in English-US (LCID 1033) formatted number formats for the second parameter (note that NumberFormat always returns English-US number format regardless of the regional setting).

 

[BQardi]

Hi, I've experienced a similar problem (I am from Denmark, and here our number formats are also different from US formats: 1.234,56).
I had to import number data from a US website, and my main problem was that if, for example, a number was 1.23 Excel would not show it as 1,23 but "see" the period as a thousand delimiter and show 1.230,00. And it seems you are experiencing something similar?
The way I solved it (I can't give you a working example, as I am currently only on a phone without Excel):
1) Converted to text
2) Replaced all comma's with a unique character, f.ex. a questionmark: "?"
3) Replaced periods with comma
4) Replaced "?" with period
5) Converted back to number
I am very positive that there is a much more elegant solution, but this "workaround" worked for me.
Maybe you can use this? Maybe just for inspiration?
I probably can't help more at the moment, but I hope you solve your problem...