Pull only specific data from 4 columns into one column

[Mickid32]

Hi,
I have 4 columns of data (AG, AH, AI, and AJ) any of the 4 columns could have the data I’d like to pull “Personal Title: (combination of numbers and letters at different lengths) into the newly created column (AK). How would I go about this?

 

[Nishant94]

=INDEX(AG2:AJ2,MATCH(1,--ISNUMBER(SEARCH("Personal Title",AG2:AJ2)),0))

 

[JamesCanale]

MrExcelPlayground23.xlsx
	A	B	C	D	E
21	Thing: John	Object: ff	Personal Title: 32342	age: 100	Personal Title: 32342
22	object: AX	Personal Title: 332334	principle: fred	email: fred@a.c	Personal Title: 332334
23	Personal Title: 112233	email: fred@a.c	objectd: asdf	What: Up	Personal Title: 112233
24	Job Title: smarty	goofy: Yes	Personal Title: 32121	Job Title: smarty	Personal Title: 32121
25	Hello: there	Who: on first	What: Where	Personal Title: 8675309	Personal Title: 8675309
Sheet25

Cell Formulas
Range	Formula
E21:E25	E21	=FILTER(A21:D21,LEFT(A21:D21,15)="Personal Title:")

 

[Mickid32]

=INDEX(AG2:AJ2,MATCH(1,--ISNUMBER(SEARCH("Personal Title",AG2:AJ2)),0))

Thank you so much this worked perfectly.

 

[Mickid32]

Thank you, so much James.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E
1
2	Thing: John	Object: ff	Personal Title: 32342	age: 100	Personal Title: 32342
3	object: AX	Personal Title: 332334	principle: fred	email: fred@a.c	Personal Title: 332334
4	Personal Title: 112233	email: fred@a.c	objectd: asdf	What: Up	Personal Title: 112233
5	Job Title: smarty	goofy: Yes	Personal Title: 32121	Job Title: smarty	Personal Title: 32121
6	Hello: there	Who: on first	What: Where	Personal Title: 8675309	Personal Title: 8675309
Data

Cell Formulas
Range	Formula
E2:E6	E2	=LET(d,TOCOL(A2:D10,1),FILTER(d,LEFT(d,15)="personal title:"))
Dynamic array formulas.

 

[alansidman]

and yet another option with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.StartsWith([Value], "Personal Title")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Other Columns"