Separate

[loknathmondal]

I Have data which is mentioned below.

[TABLE="width: 841"]
<colgroup><col></colgroup><tbody>[TR]
[TD]M/S Maharashtra Cylinders Pvt. Ltd., Near Railway Station, Post Kalmeshwar, Dist. Nagpur (Maharashtra)-[/TD]
[/TR]
[TR]
[TD]M/s. Indian Gas Cylinders, 14/1, Mathura Road, P.O. Amarngar, Faridabad –3[/TD]
[/TR]
[TR]
[TD]Universal Cylinders Ltd., 197, Matsya Industrial Area, Alwar –301030 (Raj)[/TD]
[/TR]
[TR]
[TD]M/s. Faridabad Metal Udyog Pvt. Ltd., 15/4, Mathura Road, Faridabad –121002[/TD]
[/TR]
[TR]
[TD]M/s. Interal Appliances Pvt. Ltd., Plot No. 87/3, Street No.17, MIDC, Indl. Area, Satpur, Nasik – 422007[/TD]
[/TR]
[TR]
[TD]M/s. Ideal Engineers Hyderabad Pvt. Ltd., Unit Noj.2, Plot No. 42, Co. Op. Industrial Estate, E. P. Gandhinagar, Hyderabad –37[/TD]
[/TR]
[TR]
[TD]M/s. Nagpur Fabriforge Pvt. Ltd., L-2, MIDC, Hingna Road, Nagpur –440016[/TD]
[/TR]
</tbody>[/TABLE]

I want to separate Company Name & Company Address.

If there is any formula to use or need to do manually?

Pls help


Regards
Loknath

 

[Asala42]

Based on a glance it looks like the data is comma delimited.

Try selecting the column and doing a "Text To Column" (under the Data tab/menu) with a comma checked for a delimiter.

It likely wont' be perfect though, based on what I'm seeing in your sample. (Plus some customer names could have commas in them). But it may get you most of the way there. Often these kind of problems won't have a perfect solution unless they you have a clear column delimiter/pattern or a table of known names/addresses.

 

[bosco_yip]

Try,

1] In B1, copy down:

=LEFT(A1,FIND(",",A1)-1)

2] In C1, copy down:

=SUBSTITUTE(A1,B1&", ",)

 

[loknathmondal]

Try,

1] In B1, copy down:

=LEFT(A1,FIND(",",A1)-1)

2] In C1, copy down:

=SUBSTITUTE(A1,B1&", ",)

Thank u very much.

In B1 Why I use -1 at last (for my better understanding)
In C1 why I use &", ",) (for my better understanding)


Regards
Loknath

 

[bosco_yip]

Thank u very much.

In B1 Why I use -1 at last (for my better understanding)
In C1 why I use &", ",) (for my better understanding)


Regards
Loknath

Example :

A1 : M/s. Nagpur Fabriforge Pvt. Ltd., L-2, MIDC, Hingna Road, Nagpur –440016

1] In B1 :

=LEFT(A1,FIND(",",A1)-1)
>>
=LEFT(A1,33-1)
>>
=LEFT(A1,32)
>>
="M/s. Nagpur Fabriforge Pvt. Ltd."


2] In C1 :

=SUBSTITUTE(A1,B1&", ",)
>>
=SUBSTITUTE(A1,"M/s. Nagpur Fabriforge Pvt. Ltd."&", ",)
>>
=SUBSTITUTE(A1,"M/s. Nagpur Fabriforge Pvt. Ltd., ",)
>>
="L-2, MIDC, Hingna Road, Nagpur –440016"

Regards

 

[loknathmondal]

Example :

A1 : M/s. Nagpur Fabriforge Pvt. Ltd., L-2, MIDC, Hingna Road, Nagpur –440016

1] In B1 :

=LEFT(A1,FIND(",",A1)-1)
>>
=LEFT(A1,33-1)
>>
=LEFT(A1,32)
>>
="M/s. Nagpur Fabriforge Pvt. Ltd."







2] In C1 :

=SUBSTITUTE(A1,B1&", ",)
>>
=SUBSTITUTE(A1,"M/s. Nagpur Fabriforge Pvt. Ltd."&", ",)
>>
=SUBSTITUTE(A1,"M/s. Nagpur Fabriforge Pvt. Ltd., ",)
>>
="L-2, MIDC, Hingna Road, Nagpur –440016"

Regards

I Have new data

[TABLE="width: 1379"]
<colgroup><col></colgroup><tbody>[TR]
[TD]APANJAN REALTY~~MANIKTALA, SREERAMPUR,HOOGLY~~PH-8902497136~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~[/TD]
[/TR]
[TR]
[TD]ARDHENDU DHARA~~RAJHATI, P.S-KHANAKUL~~HOOGLY-~~PH-9733969351~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Email : ARAMBAG TRANSPORT~~[/TD]
[/TR]
[TR]
[TD]BASANTI HARDWARE~~DABSHICHAK, BRAJAGOPALCHAK~~P.S:BHABANIPUR, EAST MIDNAPUR~~PH-8967721490/9002575518~~Contact : MIHIR SARKAR~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Phone : new kashi biswanath-9733946331~~[/TD]
[/TR]
[TR]
[TD]BENGAL SANITARY & MARBLES~~KUSUMGRAM BAZAR, BURDWAN~~PH-9732152636~~Contact : NEW KOLKATA ROADWAYS~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Phone : 64,STRAND ROAD--9331041343~~[/TD]
[/TR]
[TR]
[TD]BERA PIPE & SANITARY~~PARBATIPUR(GANGARHAT)TAMLUK~~PURBA MIDNAPUR,~~PH-9732916073~~Contact : SUFAL BERA~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~[/TD]
[/TR]
[TR]
[TD]DURGA SANITATION~~COLLEGE STREET~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~[/TD]
[/TR]
[TR]
[TD]KAMAL RAJ ENTERPRISES~~DIGLAbARH, SIDDHA, PANSKURA~~PURBA MIDNAPUR-721151~~PH-9932476849~~Contact : RAJARSHI SANTRA~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Phone : SRI HARI TPT / GANESH LORRY~~Email : POSTA~~[/TD]
[/TR]
[TR]
[TD]NEW MAA KALI PLUMBING & SANITARY H~~RAJHATI, P.S-KHANAKUL~~HOOGLY--~~PH-9733969351~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Phone : ARAMBAG TRANSPORT~~[/TD]
[/TR]
[TR]
[TD]NEWAGE DEVELOPER~~MANIKTALA, SREERAMPUR,HOOGLY~~PH-8902497136~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~[/TD]
[/TR]
[TR]
[TD]SAMANTA SANITATION~~BARDA, BANIPITH~~PASCHIM MEDNAPUR~~03225257902/7602284734~~Contact : MR. AJIT SAMANTA~~Gr: ABHIJIT DEALAR~~Ag: Not Applicable~~Phone : 03225-257902 , 7602284734~~[/TD]
[/TR]
[TR]
[TD]SHAKTI ELECTRIC & PIPE FITTINGS~~KUSHPATA, GHATAL, PASCHIM MIDNAPUR~~PH-9933955357~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Email : NEW BENGAL TRANSPORT~~[/TD]
[/TR]
[TR]
[TD]SHEW KUMAR AGARWAL~~GOVIND NIWAS~~60,SHAKESPEARE SARANI, KOLKATA-17~~PH-22874458/9830884466~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~Phone : BHARAT MARBLE~~[/TD]
[/TR]
[TR]
[TD]SHREE VAISHNO ASSOCIATES~~9E, NIRMAL CHANDRA STREET~~KOL-12~~PH NO. 9830340563~~Gr: ABHIJIT DEALAR~~Ag: PENDING CHALLAN~~[/TD]
[/TR]
</tbody>[/TABLE]


I want to Company Name , Address & Mobile no.


Regards
Loknath