Alphanumerical separation

[annupojupradeep]

Hi Chandoo Ji,


This is Pradeep, I have data wchich contains alphanumerica like invoice number,vendor name,systemid (e.g; fg21567 AT&T GP1) here AT&T is Vendor name. Like this I have huge data and I have to separate vendor name.

Could you please help on this


Thanks,

Pradeep

 

[prasaddn]

Hi,


Can you give more samples of your data with varied vendors names, and is the invoice number always two alpha and 5 digit number? and does the system ID always have 3 digits...


looking forward for your samples.


Regards,

Prasad DN

 

[Luke M]

My first try would be to use Data - Text to Columns to separate the data out.

 

[fred]

if vendor ID is separated with only one space " ". that'd be easy.


suppose the name is in J1,


LEFT(RIGHT(J1,LEN(J1)-FIND(" ",J1,1)),FIND(" ",RIGHT(J1,LEN(J1)-FIND(" ",J1,1)),1)-1)

 

[oldchippy]

Hi,


18 characters less than Fred


=MID(J1,FIND(" ",J1)+1,FIND(" ",J1,FIND(" ",J1)+1)-(FIND(" ",J1)+1))

 

[fred]

thumbs up oldchippy. I knew i should use mid() but too lazy cos I hardly have the chance to use it in every day life.

 

[oldchippy]

@Fred,


The main thing is that we both got the same result

 

[annupojupradeep]

Hi All thanks for guiding me.


Here is the real example in my daily work;


Column A


Ref:RP46799651130293,By order:/43662837 LAYOS SV,Pymt.Det:/RFB/EX25268614


Ref:5403102831049500,By order:/96851333 KESTREL LINER AG,Pymt.Det:/RFB/IM15622962


Ref:6202382332592900,By order:/03886484 B H SHIPPING,Pymt.Det:/RFB/0212303


Column B (Actual Parties Name to be separated)


LAYOS SV


KESTREL LINER AG


B H SHIPPING


like data I have huge data in excel sheet and I have vendor names so I need to separate these vendor names from data huge data.


Could you please help on this


Once again thanks for you all.


Regards,

Pradeep

 

[oldchippy]

Hi,


This should do the trick, assuming your list starts in A2, copy down


=MID(A2,FIND(" ",A2,FIND("/",A2)+1)+1,FIND(",",A2,FIND("/",A2)+1)-(FIND(" ",A2,FIND("/",A2)+1)+1))