Extract text after UPPERCASE text until FIND word CONTACT

[Tonex]

I need your help to extract text to other cells:

1. Address text, which always goes after UPPERCASE text
2. Contact text which goes after word Contacts:
3. Tel:
4. Fax:

Please help me with this.

I was able to extract text after UPPERCASE:

=MID(A1,MATCH(FALSE,EXACT(LEFT(A1,FIND(" ",A1&" ",ROW(INDIRECT("1:50")))),UPPER(LEFT(A1,FIND(" ",A1&" ",ROW(INDIRECT("1:50")))))),0),50)

And I was able to extract all text UNTIL Contact:


=MID(A1,1,FIND("Contact",A1,1)-1)

BUT I'm not able to extract text after UPPERCASE and until Contact


I have such table:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]COMPANY NAME Address text Contact: Text[/TD]
[TD]COMPANY NAME Tel: 01234 4567 891 Fax: 0123 4567 8910[/TD]
[/TR]
</tbody>[/TABLE]

Example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]XYZ BRAND LTD. Westfield street, 4, New York, 02504 Contact: Mr. Wong Chan[/TD]
[TD]XYZ BRAND LTD. Tel: 0123 457 0077 1 Fax: 0123 457 0954 E-mail: sales@xyzbrand.com.uk Website: www.xyzbrandmandot.co.uk[/TD]
[/TR]
</tbody>[/TABLE]

I want:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]Westfield street, 4, New York, 02504[/TD]
[TD]Mr. Wong Chan[/TD]
[TD]0123 457 0077 1[/TD]
[TD]0123 457 0954[/TD]
[/TR]
</tbody>[/TABLE]




Maybe for somebody it may help, so I will write how I was able to extract email, website and UPPER case company name with formulas (In excel Press Ctrl + Shift + Enter to use these formulas):


Extract email (Change B1 to your cell):

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("@",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))


Exctract website (Change B1 to your cell):

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("www.",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))


Exctract UPPERCASE Text (Change A1 to your cell):

=LEFT(A1,MATCH(FALSE,EXACT(LEFT(A1,FIND(" ",A1 & " ",ROW(INDIRECT("1:50")))),UPPER(LEFT(A1,FIND(" ",A1 & " ",ROW(INDIRECT("1:50")))))),0)-1)


Thank you in advance good people!

 

[István Hirsch]

At me some of your formulas do not work. Anyway, if you could extract the company name, say, into cell A2, and the whole string with the address is in A1, try this formula to extract the substring between the company name and „Contact”:

=SUBSTITUTE(LEFT(A1,FIND("Contact:",A1)-2),A2&" ","")

 

[Tonex]

What formulas doesn't work for you? Maybe I have mistaken? If still interested please explain your situation.

I have already extracted everything I need and I did almost same what you told. I believe it is more complicated than it should be, but anyway I get the results I want.

1. I have extracted company name to new cell D1 from text with this formula (it is for text with UPPERCASE) =LEFT(A1,MATCH(FALSE,EXACT(LEFT(A1,FIND(" ",A1 & " ",ROW(INDIRECT("1:50")))),UPPER(LEFT(A1,FIND(" ",A1 & " ",ROW(INDIRECT("1:50")))))),0)-1)
2. And made cell C1 with Formula =SUBSTITUTE(A1,D1,,1) So I have new cell with all text without company name.
3. And last I made another cell with this formula =MID(C1,1,FIND("Contact",C1,1)-1) It extracts information until reaches word "Contact". So I have address cell ready.


Also to extract Telephone number I made this formula: =MID(B1,SEARCH("Tel:",B1,1)+4,SEARCH("Fax:",B1,SEARCH("Tel:",B1,1)+2)-(SEARCH("Tel:",B1,1)+4)) but it works only when Fax: word is found in text, but in my list, can be Fax: or Website: words, so I have to make another formula for #VALUE! cells and change Fax: to Website:, but maybe there is another way? Search Fax: or Website: together in one formula??? SEARCH(OR("Fax:","Website") ??? I don't understand how to use this, because it is only second day of working with such excel formulas.

And another problem is that after some company names there is Ltd. word and it is not in UPPERCASE, so I'm not able to extract it together with company name to cell D1 and it goes to address cell. How can I search for Ltd. word to extract it together with Company name? What can I add to =LEFT(A1,MATCH(FALSE,EXACT(LEFT(A1,FIND(" ",A1 & " ",ROW(INDIRECT("1:50")))),UPPER(LEFT(A1,FIND(" ",A1 & " ",ROW(INDIRECT("1:50")))))),0)-1) ?

 

[Tonex]

Also I changed email extract formula to =IF(ISERROR(FIND("@",B1,1)),"",TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("@",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))) because not every list had email, so not to display #VALUE! I added IF(ISERROR(FIND("@",B1,1)),"",

 

[István Hirsch]

For example, the first formula to extract text after uppercase gives #N/A at me (perhaps because in the data there are char(160) spaces).

Just post your excel sheet to see where the data is and where you want the result. In the data column(s) you should give an example for each version you want to be processed, for example, one sample with „Website”, „Fax”, „LTD.”, „Ltd.” etc. In the result columns enter (manually) the substrings expected.

 

[Tonex]

That's ok! After you have pasted that formula, don't quit from formula line, but press "Ctrl + Shift + Enter" (On windows) and "Cmd + Shift + Enter" (On mac) and it SHOULD WORK!

 

[István Hirsch]

That's ok! After you have pasted that formula, don't quit from formula line, but press "Ctrl + Shift + Enter" (On windows) and "Cmd + Shift + Enter" (On mac) and it SHOULD WORK!

Now it works if I put a space after "LTD." (I had copied the string from your cell but this space may have disappeared from your post when displayed).

 

[Tonex]

Extract text from difficult excel situations.

The Problem with Ltd. just "FIND and REPLACE" > Ltd. to LTD.

Not sure that it is interesting for somebody, but I'll give my formulas and I think that after modifying them anyone can get results:

A2 Cell with Company name and address
B2 Cell with Contacts information
C2 Only Address and Contact (Made with this Formula, where D1 is new NAME Cell, so it starts working only after you create it) =SUBSTITUTE(A2,D2,,1)

This is what I had after scanning PDF with FineReader OCR

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 200"]
<colgroup><col></colgroup><tbody>[TR]
[TD]BRAND BC LTD. Westfield Industrial Estate, 69 Mollins Road Westfield, Cumb, B23 13HD Contact: Mr. A. Man, Managing Director[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 200"]
<colgroup><col></colgroup><tbody>[TR]
[TD]BRAND BC LTD. Tel: 0123 456 27800 5 Fax: 0123 456 7890 E-mail: info@web.uk Website: www.web.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As you can see Tel: goes after UPPERCASE (COMPANY NAME) and BEFORE Fax:, but in other cells there were no Fax:, but E-mail after Tel: or Website:, so I will show Formulas how I worked. Same thing with Fax:, sometimes after Fax: you could find E-mail:, sometimes Website:, sometimes nothing and all Tel: numbers and Fax: numbers are different length with different spaces, so it was not possible to work with length.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]FINEREADER[/TD]
[TD="align: center"]FINEREADER[/TD]
[TD="align: center"]FORMULA[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]EMAIL[/TD]
[TD="align: center"]WEB[/TD]
[TD="align: center"]TEL[/TD]
[TD="align: center"]FAX[/TD]
[TD="align: center"]ADDRESS[/TD]
[TD="align: center"]CONTACT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]

BRAND BC LTD. Westfield Industrial Estate, 69 Mollins Road Westfield,​

Cumb

, B23 13HD Contact: Mr. A. Man, Managing Director​

[/TD]
[TD]

BRAND BC LTD. Tel: 0123 456 78900 Fax: 0123 456 7890 E-mail: info@web.uk Website: www.web.uk​

[/TD]
[TD]

Westfield Industrial Estate, 69 Mollins Road Westfield,​

Cumb

, B23 13HD Contact: Mr. A. Man, Managing Director​

[/TD]
[TD]

BRAND BC LTD.​

[/TD]
[TD]

info@web.uk​

[/TD]
[TD]

www.web.uk​

[/TD]
[TD]

0123 456 78900​

[/TD]
[TD]

0123 456 7890​

[/TD]
[TD]

Westfield Industrial Estate, 69 Mollins Road Westfield,​

Cumb

, B23 13HD​

[/TD]
[TD]

Mr. A. Man, Managing Director​

[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Email[/TD]
[TD="align: center"]Web[/TD]
[TD="align: center"]Tel[/TD]
[TD="align: center"]Fax[/TD]
[TD="align: center"]Address[/TD]
[TD="align: center"]Contact[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


NAME (Extracts UPPERCASE text from cell. It is COMPANY NAME in my examples):


=LEFT(A2,MATCH(FALSE,EXACT(LEFT(A2,FIND(" ",A2 & " ",ROW(INDIRECT("1:50")))),UPPER(LEFT(A2,FIND(" ",A2 & " ",ROW(INDIRECT("1:50")))))),0)-1)




EMAIL


=IF(ISERROR(FIND("@",B2,1)),"",TRIM(RIGHT(SUBSTITUTE(LEFT(B2,FIND(" ",B2&" ",FIND("@",B2))-1)," ",REPT(" ",LEN(B2))),LEN(B2))))




WEBSITE


=IF(ISERROR(FIND("www",B2,1)),"",TRIM(RIGHT(SUBSTITUTE(LEFT(B2,FIND(" ",B2&" ",FIND("www.",B2))-1)," ",REPT(" ",LEN(B2))),LEN(B2))))




TELEPHONE


1. If "Fax or Website" is found in text after tel:
=IF(ISERROR(FIND("Fax:",B2,1)),MID(B2,SEARCH("Tel:",B2,1)+4,SEARCH("Webs",B2,SEARCH("Tel:",B2,1)+2)-(SEARCH("Tel:",B2,1)+4)),MID(B2,SEARCH("Tel:",B2,1)+4,SEARCH("Fax:",B2,SEARCH("Tel:",B2,1)+2)-(SEARCH("Tel:",B2,1)+4)))


If shows #VALUE1, then SELECT ALL COLUMN WITH #VALUE1 (IN MY EXAMPLE IT WOULD BE G COLUMN) AND PRESS F5, then press SPECIAL and SELECT SHOW FORMULA > MARK ERROR. THEN ENTER NEXT FORMULA 2 (YOU WILL BE ENTERING INTO FIRST WRONG #VALUE! CELL) and press "Ctrl + Enter" to enter changes to all selected WRONG #VALUE1 Cells. (RECHECK CELL NUMBERS, BECAUSE THEY MUST BE YOUR FIRST WRONG #VALUE CELL NUMBERS FROM YOUR TEXT IN Bx (1,2,3,....)!!!!)


2. If "E-mail" is found in text after Tel:
=IF(ISERROR(FIND("Fax:",B2,1)),MID(B2,SEARCH("Tel:",B2,1)+4,SEARCH("E-ma",B2,SEARCH("Tel:",B2,1)+2)-(SEARCH("Tel:",B2,1)+4)),MID(B2,SEARCH("Tel:",B2,1)+4,SEARCH("Fax:",B2,SEARCH("Tel:",B2,1)+2)-(SEARCH("Tel:",B2,1)+4)))



FAX


1. If "Website or E-mail" is found in text after Fax:
=IF(ISERROR(FIND("E-ma",B2,1)),MID(B2,SEARCH("Fax:",B2,1)+4,SEARCH("Webs",B2,SEARCH("Fax:",B2,1)+2)-(SEARCH("Fax:",B2,1)+4)),MID(B2,SEARCH("Fax:",B2,1)+4,SEARCH("E-ma",B2,SEARCH("Fax:",B2,1)+2)-(SEARCH("Fax:",B2,1)+4)))




2. If still shows #VALUE1, then repeat SELECT ALL ROW WITH #VALUE1 AND PRESS F5, then press SPECIAL and SELECT SHOW FORMULA >ERROR. THEN REENTER THIS FORMULA and press Ctrl + Enter. (RECHECK CELL PLACE!!!)
=IF(ISERROR(SEARCH("Fax:", B2,1)),"",RIGHT(B2,LEN(B2)-SEARCH("Fax:",B2,1)-4))




ADDRESS


=MID(C2,1,FIND("Contact",C2,1)-1)




CONTACT


=IF(ISERROR(SEARCH("Contact:", A2,1)),"",RIGHT(A2,LEN(A2)-SEARCH("Contact:",A2,1)-8))



So, I hope it may help to somebody...

 

[István Hirsch]

Re: Extract text from difficult excel situations.

When I copied your cell A2 and B2 into my spreadsheet, I got this. The data produced by Finereader should be cleaned and corrected before trying to develop reliable formulae.

Excel Workbook
	A	B
1	FINEREADER	FINEREADER
2	BRAND BC LTD. Westfield Industrial Estate, 69 Mollins Road Westfield,	BRAND BC LTD. Tel: 0123 456 78900 Fax: 0123 456 7890 E-mail:info@web.ukWebsite:www.web.uk
3	Cumb
4	, B23 13HD Contact: Mr. A. Man, Managing Director
Sheet

 

[Tonex]

Re: Extract text from difficult excel situations.

Everything works for me without mistakes, maybe you get some errors while copying from here, because I see that there are no spaces between web.uk and Website. And there is no need to clean data at Finereader for me.