TextAfter forumla

[pigsfoot]

Hi,

I have some data in a Cell A1 which is ultimately an email signature. It reads something like.

Company:AN Company
Contact: email@email.com
Telephone: 0123456789
Website: www.website.com

This continues down the sheet for 150 lines with each line being a new contact.

I need to break each type ( company, contact etc ) down into separate cells so can then use it to import into a CRM system

I have a formula in cell B1 as =TextAfter(A1,"Company: ")
The return i get is AN CompanyContact:email@email.comTelephone and so on...

I cant seem to get it to stop at the word contact and only return the company name. Any help woudl be appreciated. Thanks

 

[Joe4]

I am guessing that you have soft carriage returns in that cell.
Try this:

=TEXTBEFORE(TEXTAFTER(A1,"Company:"),CHAR(10))

 

[Fluff]

Another option if you want all the info

Fluff.xlsm
	A	B	C	D	E
1
2	Company:AN Company Contact: email@email.com Telephone: 0123456789 Website: www.website.com	AN Company	email@email.com	0123456789	www.website.com
Sheet1

Cell Formulas
Range	Formula
B2:E2	B2	=TOROW(TRIM(INDEX(TEXTSPLIT(A2,":",CHAR(10)),,2)))
Dynamic array formulas.

 

[Scott Huish]

Perhaps to get all the values
=TOROW(DROP(TEXTSPLIT(A1,":",CHAR(10)),,1),1)

 

[Cubist]

One more option:

=TEXTAFTER(TEXTSPLIT(A1,CHAR(10)),":")

 

[pigsfoot]

Fantastic, Saved me a huge job. I had already started using @Joe4 formula before i saw the rest so i'm all done. Many Thanks all, Plus 1 for the community

 

[Joe4]

You are welcome.
Glad we could help!

Note, if you do need to extract all 4 items from each cell, my solution requires you to create 4 formulas, while some of the other ones only require one formula that "spills" into the other cells with the other values.