Dividing the cell at exactly the right place

[CaptainCsaba]

Hi!

So here is the problem. We get a PDF report, and since it is not that well made, when we extract it into excel All the different columns get put into column one together. We only need a few lines from there that I managed to offset (so some cells in column D are empty) and from these lines we get to a point that is beyond my capabilities. We need to divide the cell into two different cells at exactly the right place, but because of the conversion this changes every time. I managed to figure out a way but I don't know how to put it into VBA. here are a few lines:

[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: ABN97) 1,353,255 JUPITER ASSET MANAGEMENT
[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"][TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: 16127) 325,000NFU MUTUAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: 23860) 372,461JPMFAM[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: 13465) 1,546,230 UBS ASSETMANAGEMENT[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: ECE51) 370,915225 FRANKLINST[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: EQA04) 56,593BLACKROCKINVESTMENT [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: EQA15) 110,22117F 100,TOEGYE-RO
[TABLE="width: 421"]
<tbody>[TR]
[TD="width: 421"](Ref: 85789) 304,64211-3, HAMAMATSUCHO, 2-CHOME,[/TD]
[/TR]
</tbody>[/TABLE]

So here is how the rows work. First there is the ref code (red), then the shares amount (green),
then the manager or the address of the manager (orange).[/TD]
[/TR]
</tbody>[/TABLE]

From this we need the green in a cell and the orange part in a cell. (red is not needed). As you can see, the problem is that because of the bad conversion the end of the green part is not always the same. Sometimes it ends with a space, sometimes it is together and the worst is when there is an address number after it there is no space in between. I am open to all solutions., but what I thought of is this. The end of the ref code is always the same ") ". What the macro should do is it should find this point in the cell (by the way this is column D), and after that it should copy from one up to 3 numbers. If there is a comma "," after the 3rd number it should copy the 3 numbers after that and so on until there is no comma after the 3 number.

So in the end if the base text is in Cell D38 for example is [TABLE="width: 417"]
<tbody>[TR]
[TD="width: 417"](Ref: EQA15) 110,22117F 100,TOEGYE-RO[/TD]
[/TR]
</tbody>[/TABLE]
then it should have 110,221 in Cell M38 and 17F 100,TOEGYE-RO in cell N38.

I hope this is possible. I am open to all solutions because this might not work 100% of the time, so some advice would be much appreciated.

 

[steve the fish]

So sometimes there isnt a space between the green and the orange part?

 

[CaptainCsaba]

Yes

 

[CaptainCsaba]

Also I forgot to mention, sometimes the number is below 1,000, so there is no comma in it. For example:

[TABLE="width: 421"]
<tbody>[TR]
[TD="width: 421"](Ref: ECG69) 931 6 ROUTE DE TREVES[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

I cant even think of a logical way. Lets say your post number 4. If it said 9631ROUTE DE TREVES how could that logically split that number.

 

[CaptainCsaba]

I cant even think of a logical way. Lets say your post number 4. If it said 9631ROUTE DE TREVES how could that logically split that number.

In the PDF it is 3 columns (hence the 3 colors). 931 is the amount of shares managed by somebody who had the address of "6 Route de Treves" which is probably a bank located in Luxembourg. the problem is that Adobe acrobat has a hard time when he has to decide whether there should be a space between 931 and 6 when it puts 2 columns into one. Sometimes there is, sometimes there is none.

Gave me some headaches also. That is why I am asking you guys.

 

[CaptainCsaba]

OK let's try it like this. What would you guys do if we could safely assume that there is always a space after 3 digit numbers, like in the case of "931 6 Route de Treves"?

 

[steve the fish]

What do you mean? There is always a space between the green and orange?

 

[CaptainCsaba]

What do you mean? There is always a space between the green and orange?

No. As I explained it above, the problem is that I can't solve it because sometimes there is a space and sometimes there is not. However I proposed a solution that I can't create in VBA. After that you mentioned, with a point, that that solution does not work because sometimes there could be 3 digit numbers and if there is no space after that and an address number then it could become a number where we can't determine where the 2 numbers should be divided. Therefore I said what if we created a formula where we use the solution I proposed for everything above 999 and for the cells below 1,000 we assume that there is a space (and hope that it will be like that). For everything 1,000 and above we should created something like this:
The end of the ref code is always the same ") ". What the macro should do is it should find this point in the cell (by the way this is column D), and after that it should copy from one up to 3 numbers. If there is a comma "," after the 3rd number it should copy the 3 numbers after that and so on until there is no comma after the 3 number.

 

[steve the fish]

Ok try these:

Function AddSpace(c As Range)

x = c.Value
If InStr(x, ")") > 0 Then
    x = Trim(Mid(x, InStr(x, ")") + 1))
    For i = 1 To Len(x)
        If Mid(x, i, 1) = "," Then
            If Mid(x, i + 4, 1) <> "," And Mid(x, i + 4, 1) <> " " Then
                x = Left(x, i + 3) & " " & Mid(x, i + 4, Len(x))
            End If
        End If
    Next
End If
AddSpace = x
        
End Function

Function GetNumber(c As Range)

GetNumber = Left(AddSpace(c), InStr(AddSpace(c), " ")-1)
        
End Function

Function GetAddress(c As Range)

GetAddress = Mid(AddSpace(c), InStr(AddSpace(c), " ") + 1, Len(c))
        
End Function

Put them in a standard module then use them like =GetNumber(A1) or =GetAddress(A1)