Extracting specific text from text string

[CharlieRog]

I am trying to extract a certain string of text from a bigger string of text.

I am looking to extract "OGEA********" the stars stand for numbers and each time they will be different, so the only bit of text which is the same is OGEA.

I have searched the internet and found.....

=TRIM(LEFT(SUBSTITUTE(MID(S2,FIND("OGEA",S2),LEN(S2))," ",REPT(" ",100)),100))

Where I have inputted 'OGEA' into the formula but keep getting #VALUE!

Any help please?

 

[Fluff]

Can you provide some samples of the full strings.

 

[shaowu459]

Could you show us a source string and what you want

 

[CharlieRog]

[Auto Comment]: Service Affecting set to No, at time Mon Jun 01 13:49:22 BST 2020;[Auto Comment]: Customer Details - Service ID: OGEA********;[Auto Comment]: Customer Details - Telephone Number: *********;




I have starred any customer specific data

 

[CharlieRog]

In the end I am looking to have a list of OGEA numbers and then I am trying to see if they exist in any of the cells which would have this text in. Almost like conditional formatting duplicates but I can't as it wouldn't highlight as a duplicate due to the other text in the cell

 

[shaowu459]

Is this what you want?

Book1
	A	B
1	[Auto Comment]: Service Affecting set to No, at time Mon Jun 01 13:49:22 BST 2020
2	[Auto Comment]: Customer Details - Service ID: OGEA********	OGEA********
3	[Auto Comment]: Customer Details - Telephone Number: *********
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=IFERROR(MID(A1,FIND("OGEA",A1),99),"")

 

[Fluff]

How about
=TRIM(LEFT(SUBSTITUTE(REPLACE(S2,1,SEARCH("ogea",S2&"ogea")-1,""),";",REPT(" ",100)),100))

 

[CharlieRog]

How about
=TRIM(LEFT(SUBSTITUTE(REPLACE(S2,1,SEARCH("ogea",S2&"ogea")-1,""),";",REPT(" ",100)),100))

This worked perfectly! Thanks everyone

 

[Fluff]

Glad we could help & thanks for the feedback.