I need your help to extract text to other cells:
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:
<tbody>
</tbody>
Example:
<tbody>
</tbody>
I want:
<tbody>
</tbody>
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!
- Address text, which always goes after UPPERCASE text
- Contact text which goes after word Contacts:
- Tel:
- 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:
A | B |
COMPANY NAME Address text Contact: Text | COMPANY NAME Tel: 01234 4567 891 Fax: 0123 4567 8910 |
<tbody>
</tbody>
Example:
A | B |
XYZ BRAND LTD. Westfield street, 4, New York, 02504 Contact: Mr. Wong Chan | XYZ BRAND LTD. Tel: 0123 457 0077 1 Fax: 0123 457 0954 E-mail: sales@xyzbrand.com.uk Website: www.xyzbrandmandot.co.uk |
<tbody>
</tbody>
I want:
A | B | C | D |
Westfield street, 4, New York, 02504 | Mr. Wong Chan | 0123 457 0077 1 | 0123 457 0954 |
<tbody>
</tbody>
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!