Grabbing Text After Certain Character & More

[mdsurf]

I have a cell formatted in the following way

G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^New Game^My New Symbol^Company Flag^Don't Want^Oregon^.png

What I want to do is extract most of the words after the first '^' character between the string of numbers and 'New Game'. The end result should be

New Game My New Symbol Company Flag Oregon

Please note how the 'don't want' isn't included. I need to remove this text but not the text specifically, but the fact that it's the 4th batch of text between '^' if that makes sense.

Thank you in advance.

 

[maabadi]

Try:

Book1
	F	G	H
1
2		G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^New Game^My New Symbol^Company Flag^Don't Want^Oregon^.png
3
4	Step 1	New Game^My New Symbol^Company Flag^Don't Want^Oregon^.png
5	Step 2	New Game^My New Symbol^Company Flag^Don't Want^Oregon^
6	Step 3	New Game My New Symbol Company Flag Don't Want Oregon
7
8	All in one	New Game My New Symbol Company Flag Don't Want Oregon
9
Sheet6

Cell Formulas
Range	Formula
G4	G4	=RIGHT(G2, LEN(G2) - FIND(CHAR(94),G2))
G5	G5	=LEFT(G4,FIND(".",G4)-1)
G6	G6	=TRIM(SUBSTITUTE(G5,CHAR(94)," "))
G8	G8	=TRIM(SUBSTITUTE(LEFT(RIGHT(G2, LEN(G2) - FIND(CHAR(94),G2)),FIND(".",RIGHT(G2, LEN(G2) - FIND(CHAR(94),G2)))-1),CHAR(94)," "))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Could you also give 3 or 4 more varied data samples and the expected results?

 

[JEC]

To comply with this:

Please note how the 'don't want' isn't included.

When the format is always the same (6 times "^" )

Function jec(cell As String) As String
 a = Split(cell, "^"): a(0) = "": a(4) = "": a(6) = ""
 jec = Application.Trim(Join(a))
End Function

 

[Sufiyan97]

Try

Book1
	A	B	C
1	G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^New Game^My New Symbol^Company Flag^Don't Want^Oregon^.png	New Game My New Symbol Company Flag Oregon
2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(LEFT(MID(SUBSTITUTE(A1,CHAR(94),"@",4),FIND(CHAR(94),SUBSTITUTE(A1,CHAR(94),"@",4))+1,99),FIND("@",MID(SUBSTITUTE(A1,CHAR(94),"@",4),FIND(CHAR(94),SUBSTITUTE(A1,CHAR(94),"@",4)),99))-1)&" "&TRIM(RIGHT(REPLACE(SUBSTITUTE(A1,CHAR(94),REPT(" ",99)),FIND(".",SUBSTITUTE(A1,CHAR(94),REPT(" ",99))),99,""),99)),CHAR(94)," "),"@","")

 

[Sufiyan97]

Ignore post #5 and Try to exclude batch #4

Book1
	A	B
1	G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^New Game^My New Symbol^Company Flag^Don't Want^Oregon^.png	New Game My New Symbol Company Flag Oregon
2	G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^New Game^My New Symbol^Company Flag^Don't Want^Oregon^Ashab^.png	New Game My New Symbol Company Flag Oregon Ashab
3	G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^Ali^New Game^My New Symbol^Company Flag^Don't Want^Oregon^Ashab^.png	Ali New Game My New Symbol Don't Want Oregon Ashab
4	G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^Ali^Pravin^New Game^My New Symbol^Company Flag^Don't Want^Oregon^Ashab^Pooja^.png	Ali Pravin New Game Company Flag Don't Want Oregon Ashab Pooja
5
6
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(SUBSTITUTE(SUBSTITUTE(CONCATENATE(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND(CHAR(94),A1),""),CHAR(94),"@",3),FIND("@",SUBSTITUTE(REPLACE(A1,1,FIND(CHAR(94),A1),""),CHAR(94),"@",3))-1)," ",REPLACE(REPLACE(SUBSTITUTE(REPLACE(A1,1,FIND(CHAR(94),A1),""),CHAR(94),"@",3),1,FIND("@",SUBSTITUTE(REPLACE(A1,1,FIND(CHAR(94),A1),""),CHAR(94),"@",3)),""),1,FIND(CHAR(94),REPLACE(SUBSTITUTE(REPLACE(A1,1,FIND(CHAR(94),A1),""),CHAR(94),"@",3),1,FIND("@",SUBSTITUTE(REPLACE(A1,1,FIND(CHAR(94),A1),""),CHAR(94),"@",3)),"")),"")),".png",""),CHAR(94)," "))

 

[Peter_SSs]

If there are always the same number of sections (that's why I asked for more samples) then these worksheet functions should do the job
G3 if MS 365 or Excel 2019 (possibly Ctrl+Shift+Enter for 2019 but not for 365)
G4 for any version

mdsurf.xlsm
	G
2	G:\Shared drives\Designs\AB Acc\My Saw\PR Batch12\10021290403^New Game^My New Symbol^Company Flag^Don't Want^Oregon^.png
3	New Game My New Symbol Company Flag Oregon
4	New Game My New Symbol Company Flag Oregon
Sheet1

Cell Formulas
Range	Formula
G3	G3	=TRIM(CONCAT(MID(SUBSTITUTE(G2,"^",REPT(" ",300)),{1,2,3,5}*300,300)))
G4	G4	=TRIM(REPLACE(REPLACE(REPLACE(SUBSTITUTE(G2,"^",REPT(" ",300)),1800,300,""),1200,300,""),1,300,""))