lookup but cell has many rows of text :-(

[John Caines]

Hello All,
I really hope someone can help me out here if possible.
I'm trying to extract some data from a cell, but it has a lot of rows of text, that makes it difficult (Well, for me anyway!)

Here's an example 1st, then I'll explain what I'm tring to do,
EXAMPLE:

NO	URL	Header	Server	X-Powered-By
1	http://247-services.co.uk/	HTTP/1.1 200 OK Connection: Upgrade, Keep-Alive Date: Fri, 09 Oct 2020 13:23:12 GMT Keep-Alive: timeout=2, max=100 Transfer-Encoding: chunked Upgrade: h2,h2c Content-Type: text/html; charset=UTF-8 Server: Apache X-Powered-By: PHP/7.0.33 Link: <http://247-services.co.uk/wp-json/>; rel="REST API Handbook | WordPress Developer Resources", <http://247-services.co.uk/>; rel=shortlink

1-I have several 100 of these and I need to extract;
1- Server:
2-X-Powered-By:
So basically In Column D (Server I need it to return the word 'Apache'
& in Column E (X-Powered-By I need it to return the value 'PHP/7.0.33'
The returned values are always on the same line.
And of course the returned values can be different, IE not just the word apache, could be Litespeed or PHP might be PHP/5.6 etc as examples.

I hope someone can help me out here as I really am stuck with how to do this.
Many thanks in advance

Best regards to you all
Stay safe!
John Caines

#and a big shoutout to whoever has made this post software, so you can easily copy/paste a nice table into the thread! Amazing!

 

[Eric W]

How about:

Book1
	A	B	C	D	E
1	NO	URL	Header	Server	X-Powered-By
2	1	http://247-services.co.uk/	HTTP/1.1 200 OK Connection: Upgrade, Keep-Alive Date: Fri, 09 Oct 2020 13:23:12 GMT Keep-Alive: timeout=2, max=100 Transfer-Encoding: chunked Upgrade: h2,h2c Content-Type: text/html; charset=UTF-8 Server: Apache X-Powered-By: PHP/7.0.33 Link: <http://247-services.co.uk/wp-json/>; rel="REST API Handbook | WordPress Developer Resources", <http://247-services.co.uk/>; rel=shortlink	Apache	PHP/7.0.33
Sheet11

Cell Formulas
Range	Formula
D2:E2	D2	=TRIM(RIGHT(SUBSTITUTE(LEFT($C2,FIND(CHAR(10),$C2,FIND(D$1,$C2))-1),":",REPT(" ",99)),99))

 

[John Caines]

Hi Eric W!
Many thanks for your reply!

I actually was thinking it might be a formula like this (Not that I really have much of a clue LOL), as I was on this link, (Googled) and trying;

Extract word that begins with specific character

To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user name from a cell that contains other text. In the example shown, the formula...

exceljet.net

=TRIM(LEFT(SUBSTITUTE(MID(C2,FIND("Server:",C2),LEN(C2))," ",REPT(" ",100)),100))
Hey, totallly wrong, but kind of on the right track!

Many thanks again for your reply Eric.
I've just tried your formula in both columns, and it's working perfectly!

Many thanks again for your reply !
Saved my bacon!

Have a great day!
Best regards Eric
A very thankful
John Caines

 

[Eric W]

Definitely on the right track! Glad to help! Thanks for the feedback.

(I just noticed - your avatar really scared my avatar! ? )

 

[John Caines]

Thanks again Eric for your help. Haha! The avatar, yes! If I remember rightly, when I very 1st joined I wanted my user name to be 'The Ghost' many years back and it got refused! Too scarey LOL.
Have a great day

Cheers Eric
John C