Remove some text from VLOOKUP result

[Tomo1971]

Hi,

I have a vlookup that extracts two names from one cell on another tracker

=VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE)

This gives the correct result of;

John Smith & Joe Bloggs

I need to separate both these names into two cells,

Cell A14 would need to be John Smith =LEFT(A15,FIND("&",A15)-1)
Cell A15 would need to be Joe Bloggs =RIGHT(A14,LEN(A14)-FIND("&",A14))

So in other words, the text in A14 before the & and the text in A15 after the &

Is this possible?

Many thanks

 

[FormR]

Hi, you could try these..

A14: =TRIM(LEFT(SUBSTITUTE(VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE),"&",REPT(" ",255)),255))
A15: =TRIM(RIGHT(SUBSTITUTE(VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE),"&",REPT(" ",255)),255))

 

[Scott T]

You can put the vlookup formula in place of the cell reference

=LEFT(VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE),FIND("&",VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE))-1)

=RIGHT(VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE),LEN(A13)-FIND("&",VLOOKUP(B4,'[Project Tracker.xlsx]MAIN'!$1:$1048576,114,FALSE)))

 

[Tomo1971]

Thank you both, the first post works perfectly