Paste from varied source formats XL2010

[jxd078]

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:

[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!