Parsing decimal numbers from Text and putting it in separate column.

[satishsahoo]

Dear All,
I have data in the following format in column A.
Input looks like-
row 1-Scottish Widows Investment Management Ltd 3,602,689 3.36
row 2-Banco Santander Central Hispano S.A. 9.62

Column A is a mixture of name(Scottish Widows Investment Management Ltd) and numbers (number of shares(3,602,689),% holding(3.36)). But separating on space doesn't work as the number of spaces is not constant. Also some rows have both number of shares and % holding(row 1) and some only have the % holding (row 2) as shown above. I need to split this in to two columns to have the name and the %holding in separate columns. I don't care about the number of shares.
The out put looks like-
Name % holding
Scottish Widows Investment Management Ltd 3.36
Banco Santander Central Hispano S.A. 9.62

'''''''''''''''''''''''''''''''''
The order of number of share and % holding might be different for different row. I.e row 1 can have % holding first and then number of shares and row 2 might have it in reverse order. As stated above in some cases the number of shares might be missing.
So I need to parse based on the ".' decimal present in the percentage holding. So it is always in the form of 3.0/12.5 etc. So I need to look for the '.' which is preceded and followed by a number and put the entire thing in to a column. So some sort of regular expression needs to be used to isolate the decimal numbers.
The number of shares is always a whole number without decimal and is comma separated. So that can be distinguished.

Please help me with a macro that can accomplish this.
I would have loved to upload a sample file but no clue how to do it. Let me know if there is any confusion.
Thanking you all a lot for taking time out.

 

[pgc01]

I copied it in to a new module which was by default named module1, I renamed it as ExtractData and then invoked it from xls.

You're welcome. Thanks for the feedback.

Don't rename the module to the same name as the function. It will create confusion, you'll have 2 different entities with the same name.