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.

 

[kevatarvind]

pls upload a sample file with desired result any other sharing site and then paste that link here

 

[BenMiller]

Can you not just be looking for the last space?

Company Name: =MID(A1,1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

% Holding:
=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

[Typed on my phone and not tested. Double spaces/trailing spaces will screw up these formulas.]

 

[satishsahoo]

Please find the sample file attached. Thanks

https://docs.google.com/file/d/0Bycha9VkOvpKdUd3bnhwdTR0Skk/edit?usp=sharing

 

[satishsahoo]

The problem is number of spaces are not constant and data is really messy as can be seen from the uploaded sample file. After going through some 400,500 entries I am sure the only trick that would work is if we can isolate the number with decimal in it. The other number i.e number of shares is a whole number.

 

[pgc01]

Hi

1 - If you want to post a sample with data it usually works copying directly a range from excel to the IE. You can put borders in the cells.
Remark:
Many of the board users will not follow a link.
A link to a file is also not a good idea since, as you know, it will be dead in a couple of months, rendering this thread useless to anyone with a similar problem.
Always try to post the data directly.

2 - If I understand correctly, try this udf.

Function ExtractData(s As String, lInd As Long)

With CreateObject("VBScript.RegExp")
    .Pattern = "(\D+)\s.*?(\d+\.\d+)"
    If .test(s) Then If lInd = 1 Or lInd = 2 Then ExtractData = .Execute(s)(0).submatches(lInd - 1)
End With
End Function

The second parameter has the value 1 for Name and 2 for %.

Ex.:
=ExtractData(A2,1) ' Gets the Name
=ExtractData(A2,2) ' Gets the %


Test:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Name</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">% holding</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Scottish Widows Investment Management Lt<br>d 3,602,689 3.36</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Scottish Widows Investment Management Lt<br>d</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">3.36</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Banco Santander Central Hispano S.A. 9.6<br>2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Banco Santander Central Hispano S.A.</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">9.62</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">My company.com 12.34 1,234,567</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">My company.com</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">12.34</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=5 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>

 

[satishsahoo]

Thanks pgc01. I tried creating and running the UDF. But when I run it I get #NAME? Error. I tried running other sample UDFs and they are running. Does this UDF require any specific formatting of input column ? I am running =ExtractData(A2,2) and A2 has

Fidelity Investments 3,969,185 13.8

<tbody>
</tbody>

Please guide. Thanks

 

[pgc01]

Hi

Where did you paste the code?

You have to paste the code in a standard module (if it is the first module that you insert it has the name Module1).

 

[satishsahoo]

Hi,
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. When I type Ex the xls is showing me the function in the list of functions. So I don't think there is an issue there. Plus in the same module i.e ExtractData I pasted another simple UDF which calculates discounts and it runs. So not sure what is causing this to fail. As you have mentioned in your last post it runs in your system. Please suggest. When I run an evaluate function it says #NAME(A2,1) .
Thanks

 

[satishsahoo]

Hi,
I was just playing around with it and renamed the ExtractData module as module1 and now it is running. I also set the security level to minimum. And the code works perfectly. That is a great help pgc01. Also thanks to ben and arvind for replying.
If you guys can please let me know why the renaming as module1 works it would be great for future reference. I am not sure why I can't rename those modules.Thanks