How should I tell the difference?

[CaptainCsaba]

Hey Everyone!

I need to create a macro to make come reports easier to handle. One of them looks like this when it gets put into excel. Basically it tells who the owners are to certain stocks. I need only the rows where it tells the stock owners only. A cell like that looks like this for example: 123,456 XYZ llc.
The number is the share amount and after that is the owner. I would need to get these lines. The problems is that there are cells below this which tell the adress, eg 555 Baker street.
I have no idea on what to write to distinguish these. I thought about using the "," as a reference but ten everything that is below 1,000 does not get added. Another problem si that when it gets converted from the PDF file it will not always be perfect,so sometimes weird symbols will get added for example and basically it will not be like "the needed cell is every 5th in a line" or "it is always 3 cells below a cell that looks like this". So I am kinda stuck here. I'll leave an example below ( i had to change the names for confidentiality). The lines we need are red. How should I solve this?

NORWAY--------------

12,700 ABC llc

RE HASPA MULTIINVEST

3 RUE DES LABOURg

.

.

L-1912

LUXEMBOURG

Beneficial Owner ref

Designation of LUXAI

(Ref: ABX89) 12,700

OE 26210;

HAHNSTRASSE 55

D-60528 FRANKFURT AM

GERMANY-------------

12,663 DEF llc

LEVEL 26

101 COLLINS STREET

MELBOURNE

VICTORIA

3000

AUSTRALIA

Beneficial Owner ref

(Ref: 19101) 12,663

GROSVENOR PLACE,

225 GEORGE STREET

SYDNEY NS 0001

AU------------------

12,581 GHI llc

11-3 HAMAMATSUCHO

2-CHOME, MINATO-KU

TOKY2

105-8579

JAPAN

Beneficial Owner ref

Designation of M7614

(Ref: EEU48) 12,581

MINATO-K=

TOKYO 1058579

JP------------------

12,306 IJK llc

2 OAKWOOD COURT

LITTLE OAK DRIVE

ANNESLEY

NOTTINGHAMSHIRE

NG15 DDR

UNITED KINGDOM

Beneficial Owner ref

(Ref: 45769) 12,306

MANAGEMENT LTD

31 GRESHAM STREET LO

EC2V 7QA UK---------

12,016 XYZ llc

Designation of JPMAG

(Ref: EBH83) 12,016

FRANKFURT AM MAIN

DE - FEDERAL REPUBLI

60329---------------

11,895 WCG llc

LANDSVAGEN 41

SUNDBYBER<

SE-172 6F

SWEDEN

Beneficial Owner ref

(Ref: BAP41) 11,895

BJORN MOLLER E62

SE-105 34 STOCKHOL:

<colgroup><col></colgroup><tbody>
</tbody>

 

[CaptainCsaba]

in post#7 you said
Can you please show some examples of this, highlighting the values that you want?

We get these as PDF-s. Adobe acrobat converts them to excel and we have something like this in the end:

.

3,223,969 THE JUPITE R GLOBAL FUND S ICAV

6 ROUTE DE TREVEg

SENNINGERBER<

L-2633

LUXEMBOURG

Beneficial Owner ref : 111289

Designation of LUXUC Ip

(Ref: ACE67) 3,142,1 87 JUPITE R AS SE T MA NAGEMENT

THE ZIG ZAG, 70 VICT ORIA STRE ET

LONDON SW1E 6SQ

UNITED KINGDOM

(Ref: AXZ33) 80,851 JUPITER A SSET M ANAG EMENT

THE ZIG ZAG, 70 VICT ORIA STRE ET

LONDON SW1E 6SQ

UNITED KINGDO:

(Ref: ECG69) 931 6 R OUTE DE T REVE S

SENNINGERBERG

LU - LUXEMBOURG

L-2633-------------- --------- ---- -- ---- ---------------------------------------"

2,372,777 VANGUARD T OTAL INTE RNAT IO NAL STOC&

INDEX FUND

100 VANGUARD BOULEVA Rr

MALVERN

PENNSYLVANIA

19355

U.S.A

Beneficial Owner ref : 101961

Designation of BBHLE Nr

(Ref: 51576) 2,372,7 77 THE VA NGUA RD GRO UP INC

PO BOX 1102 MAILSTOP A29

VALLEY FORGE PA

19355 UNITED STATES

ATTN: CHRISTOPHER WI GHTMAN--- ---- -- ---- -----------------------------------------------------------"

1,546,230 TAMESIDE M BC RE GRE ATER M ANCH ESTER

PENSION FUND

5 MANCHESTER ROAr

DROYLSDEN

GREATER MANCHESTER

M43 6SF

UNITED KINGDOM

Beneficial Owner ref : 76657

Designation of TMBC$

(Ref: 13465) 1,546,2 30 UBS AS SET MA NAGE MENT

21 LOMBARD STREET

LONDON EC3V 9AH

(a page break was here, the "line does not appear -CaptainCsaba)

1,353,255 JUPITER EU ROPEAN OP PORT UN ITIE S

TRUST PLC

THE ZIG ZAG BUILDING

70 VICTORIA STREET

LONDOj

SW1E 6SQ

UNITED KINGDOM

Beneficial Owner ref : 110718

Designation of JPMEL AI)

(Ref: ABN97) 1,353,2 55 JUPITE R AS SE T MA NAGEMENT

THE ZIG ZAG, 70 VICT ORIA STRE ET

LONDON SW1E 6SQ

<colgroup><col></colgroup><tbody>
</tbody>

Every line is basically one big cell, so what you see here is only the "A" column. What I want is the red cells to get put into The B column next to them. So if the "3,223,969 THE JUPITE R GLOBAL FUND S ICAV" is in A33, then it should be moved into B33. What makes this hard is that there are alot of similar cells. For example "70 VICTORIA STREET" is the same format, the only difference is that it does not have an "," in it. So I guess we should use if we don't have a better idea.

 

[Fluff]

Give this a go, it will probably need some further tweaks

Sub GetData()

   Dim Rng As Range
   Dim Cl As Range
   
   With Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(isnumber(value(left(@,find("" "",@)-1))),@,"""")", "@", .Offset(, -1).Address))
      For Each Rng In .SpecialCells(xlConstants).Areas
         For Each Cl In Rng
            If InStr(1, Cl.Value, ",") = 0 Then Cl.ClearContents
         Next Cl
      Next Rng
      .SpecialCells(xlConstants).Copy Range("D1")
      .ClearContents
   End With

End Sub

 

[CaptainCsaba]

Thank you for the help! I left the office for the week, but I will give it a try on mondy morning. I will write the results. Thank you for the help again!

 

[Fluff]

Glad to help & have a good weekend.

Look forward to hearing the results, but will probably need a few tweaks.