Paste from varied source formats XL2010

jxd078

New Member
Joined
Sep 18, 2015
Messages
3
Hi,

I am working on a data collection tool that will be used by people across various different countries, unlikely to be of a high competence in excel.

The macro I require is a glorified paste special function, which will be added both as a button and a keyboard short cut.

The issue that I am facing is due to uncertainty of the sources from which people will be pasting into the tool.
There are several hurdles to overcome in terms of the possible different formats that could be in the clipboard before pasting, as I need the final output to always be in the US numeric format ('.' decimal separator and ',' thousand separator).

Source data could be in the following formats:

Code:
[TABLE="width: 535"]
<tbody>[TR]
[TD]US Numeric[/TD]
[TD="align: right"]4,500[/TD]
[TD="align: right"]2.29[/TD]
[TD="align: right"]2.11[/TD]
[TD="align: right"]1.94[/TD]
[TD="align: right"]1.76[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]1.19[/TD]
[TD="align: right"]1.08[/TD]
[TD="align: right"]1.07[/TD]
[TD="align: right"]1.07[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]£4,500.00[/TD]
[TD="align: right"]£1.68[/TD]
[TD="align: right"]£1.60[/TD]
[TD="align: right"]£1.53[/TD]
[TD="align: right"]£1.46[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]£30,000.00[/TD]
[TD="align: right"]£1.21[/TD]
[TD="align: right"]£1.06[/TD]
[TD="align: right"]£0.99[/TD]
[TD="align: right"]£0.95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US Numeric as Text[/TD]
[TD]4,445.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US Numeric as Text w/ Currency[/TD]
[TD]£4,455.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EU Numeric as Text[/TD]
[TD]4.500[/TD]
[TD]1,68[/TD]
[TD]1,6[/TD]
[TD]1,53[/TD]
[TD]1,46[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3.000[/TD]
[TD]1,21[/TD]
[TD]1,06[/TD]
[TD]0,99[/TD]
[TD]0,95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EU Numeric as Text w/Currency[/TD]
[TD]$4.500[/TD]
[TD]$1,68[/TD]
[TD]$1,6[/TD]
[TD]$1,53[/TD]
[TD]$1,46[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]$3.000[/TD]
[TD]$1,21[/TD]
[TD]$1,06[/TD]
[TD]$0,99[/TD]
[TD]$0,95[/TD]
[/TR]
</tbody>[/TABLE]

How Excel seems to act also depends on whether or not the source is open in the same instance of Excel, or a different one. In theory the data could also be copied directly from an email or other application.

Some users may be copying from an Excel workbook in which the default numeric settings are the European number formats: ',' decimal separator and '.' thousand separator.

I feel like this must be a problem people have had to overcome before, however I cannot find anything on the internet that can solve the entire problem. I have been individually able to navigate around certain formats, but always jeopardising the ability to paste another.

If it helps, I have included an activex toggle by which the user must select which numeric format their data is in, before pasting, I thought this may help to allow two different macros depending on the format.

Thanks!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

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