lookup but cell has many rows of text :-(

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,127
Office Version
  1. 2019
Platform
  1. Windows
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:
NOURLHeaderServerX-Powered-By
1http://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!

 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
How about:

Book1
ABCDE
1NOURLHeaderServerX-Powered-By
21http://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=shortlinkApachePHP/7.0.33
Sheet11
Cell Formulas
RangeFormula
D2:E2D2=TRIM(RIGHT(SUBSTITUTE(LEFT($C2,FIND(CHAR(10),$C2,FIND(D$1,$C2))-1),":",REPT(" ",99)),99))
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,127
Office Version
  1. 2019
Platform
  1. Windows
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;
=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
Definitely on the right track! Glad to help! Thanks for the feedback. :cool:

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

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,127
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,929
Messages
5,545,082
Members
410,652
Latest member
Zot
Top