Using a Left and Mid formula with a Vlookup

[Hwatsonnn]

I have a Vlookup:

=IFNA(VLOOKUP($A4,'ON 2019'!$A:$H,COLUMN(),0),VLOOKUP($A4,'ON 2018'!$A:$H,COLUMN(),0))

Is it possible to then have this formula working after the Vlookup has worked out:

=LEFT(B4,8)&" "&MID(B4,9,6)

Thanks for any help

 

[steve the fish]

Not exactly clear what you ask for. Do you mean you want the lookup value to be =LEFT(B4,8)&" "&MID(B4,9,6)? If so just replace both $A4 with LEFT(B4,8)&" "&MID(B4,9,6)

 

[RoryA]

If B4 in the formula represents the result of the VLOOKUP, you could use:

=REPLACE(LEFT(IFNA(VLOOKUP($A4,'ON 2019'!$A:$H,COLUMN(),0),VLOOKUP($A4,'ON 2018'!$A:$H,COLUMN(),0)),14),9,0," ")

If the VOOKUP result is only 14 characters, you don't need the LEFT function.

 

[Hwatsonnn]

I get a value returned that is 16 characters long, and I just wanted it to format nicely using that formula. I wasn't sure whether I could incorporate it into the the same cell.
Thanks

 

[Hwatsonnn]

If B4 in the formula represents the result of the VLOOKUP, you could use:

=REPLACE(LEFT(IFNA(VLOOKUP($A4,'ON 2019'!$A:$H,COLUMN(),0),VLOOKUP($A4,'ON 2018'!$A:$H,COLUMN(),0)),14),9,0," ")

If the VOOKUP result is only 14 characters, you don't need the LEFT function.

That worked perfectly, thanks a lot.