Easiest n fastest way to split string into two different cells

[saidhruv]

Dear All,

I have a string in one cell that has Company Name as well as Address together. It is required that Company Name and Address be separated in different cells. Now, the delimiter to what is the end of Company Name and what is the start of address varies almost always. Hence, currently what i do is, double click on the string where the company name ends and then click alt+enter. This gives me a delimiter Char(10). And in cell B and C i have written the following formula.
Cell B1: =LEFT(A1,(FIND(CHAR(10),A1,1)-1))
Cell C1: =MID(A1,SEARCH(CHAR(10),A1:A1),500)

e.g. here the company name ends at DIV and address starts from American.. Hence i click alt+enter after DIV in A1.. Hence having written the formula in B1 and C1 the output would be..
A1 = LELE LABORATORY DIV AMERICAN CMID CO"2101 Wason St., E.Cston, V 255Wsin"
B1 = LELE LABORATORY DIV
C1 = AMERICAN CMID CO"2101 Wason St., E.Cston, V 255Wsin"

Query: I have many many such rows. Can someone help me the fastest way to delimit with Char(10). What i do is, do a double click at DIV and thereby click alt enter. Instead of alt enter can something faster be done here?
Or, is there any other way out to differentiate company name and address. Pls remember, the delimiter always changes, hence formula/vba to search a delimiter is not an option.

Thanks!

 

[bobhc]

A upload of your working data would help.
Why not use text to columns.

 

[saidhruv]

Dear bobhc - pls find the sample of working data. and i have almost 1000 such rows.

 

[Khalid NGO]

Hi,

Enter this in B3:
=LEFT(A3,IFERROR(FIND("DIV",A3)+2,FIND("""",A3,1)-1))

and this in C3:
=SUBSTITUTE(A3,B3,"")

Copy down both.

Regards,

 

[saidhruv]

Hi,

Enter this in B3:
=LEFT(A3,IFERROR(FIND("DIV",A3)+2,FIND("""",A3,1)-1))

and this in C3:
=SUBSTITUTE(A3,B3,"")

Copy down both.

Regards,

Dear Khalid, thanks a lot for the reply.
We have a string like this:

STERWIN CHEMS INC. SUB STERLING DRUG INC."946 Kane Street Toledo OH 43612 t:+1 419 478 0165 f:+1 419 478 0191"

and the output in this case would be
STERWIN CHEMS INC. as company name while address would be:
SUB STERLING DRUG INC."946 Kane Street Toledo OH 43612 t:+1 419 478 0165 f:+1 419 478 0191"


Hence, since there are manual ways to identify where the company name ends, it would help to know how fastest can the string be separated.

 

[vletm]

You could test this possible...
NOTE: Write ONLY those BOLD-parts
1) Write to Cell[A1] ... DIV
and
press <ENTER>
2) Write to Cell[A1] ... INC.
and
press <ENTER>
3) Continue as above until all 'keyword' have written.

... something like this?

 

[saidhruv]

You could test this possible...
NOTE: Write ONLY those BOLD-parts
1) Write to Cell[A1] ... DIV
and
press <ENTER>
2) Write to Cell[A1] ... INC.
and
press <ENTER>
3) Continue as above until all 'keyword' have written.

... something like this?

thanks a lot vletm.. but what do i say.. cause there are variations in the data. at times DIV and INC both come in same string and either of them can be the latter one. Hence, if i write DIV the string will be cut from DIV even if the data had INC before DIV..
Example:
data1: STERWIN CHEMS DIV. SUB STERLING DRUG INC."946 Kane Street Toledo OH 43612 t:+1 419 478 0165 f:+1 419 478 0191"

data2: STERWIN CHEMS INC. SUB STERLING DRUG DIV."946 Kane Street Toledo OH 43612 t:+1 419 478 0165 f:+1 419 478 0191"

Output for data1:
Company Name: STERWIN CHEMS DIV.
Address: SUB STERLING DRUG INC."946 Kane Street Toledo OH 43612 t:+1 419 478 0165 f:+1 419 478 0191"

Output for data2:
Company Name: STERWIN CHEMS INC.
Address: SUB STERLING DRUG DIV."946 Kane Street Toledo OH 43612 t:+1 419 478 0165 f:+1 419 478 0191"

Hence, though your code is so so well written, but the data does not allow me to use it..

Instead, if you can tell me any other way to just double click on the delimiter due to which it automatically sends data to Company Name and Address columns respctively. Currently using mouse i double click just after DIV or INC whatever is required and thereby click on alt+enter.

Thanks!

P.S. - i feel i should continue doing the manual work, since there can be no other way of speeding up the manual work. I was assuming that we can change the code in such a manner that just be doing double click within the cell should extract data as required. Is there a way to avoid click alt+enter.

Thanks again vletm for your patience and help..

 

[vletm]

... INC. / DIV or what ever ...
that's why You can select which 'parameter' do You want to use
and all those would move to B/C-columns.
if if wrong order then 'just return back to A-column'.
It would be 'better' if there could be 'rule' which could use ( for example always "|" between name and address).
... of course this could modify ...
You could write many parameters (DIV|INC.|YES) in Cell[A1] and
if more than one parameter would find then user should select,
which parameter should use. Those 'manually selected' cells could act while running or after all 'clear' has done...

 

[bosco_yip]

Try.......

1] B3, CTRL+SHIFT+ENTER array formula and copy down :

=LEFT(A3,MIN(IFERROR(FIND({"DIV","INC"},A3),9^9))+2)&IF(MID(A3,MIN(IFERROR(FIND({"DIV","INC"},A3),9^9))+3,1)=".",".","")

2] C3, enter formula and copy down :

=TRIM(SUBSTITUTE(A3,B3,""))

Regards
Bosco

 

[vletm]

@saidhruv
As I wrote ...
You could write max 20 FIND-parameter in Cell[A1].
Use "|" between parameter as in sample.
If there are 'multi-possibilities' then You will apply which parameter should use.