Copy formatted cell values to another range as text

ttt123

Board Regular
Joined
May 31, 2006
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
Hi ttt123
Instead of:
Code:
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:
Code:
set B1's Formula to: =TEXT(A1,"[$€-2] #.##0,00")
set B2's Formula to: =TEXT(A2,"0,00%")
 

ttt123

Board Regular
Joined
May 31, 2006
Messages
116
Office Version
  1. 365
Platform
  1. Windows
Hi ttt123
Instead of:
Code:
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:
Code:
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).
 
Last edited:

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,940
Members
416,953
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top