Mar 08 2023 08:14 AM
Hello,
As you will see from the attached image, I want to be able to pull the last five numbers from the header called VIN, the letters from MAKE, to make an automatic code under STOCK CODE.
I hope I have explained myself, and if so, is it possible?
Thanks
Mar 08 2023 08:28 AM
=VLOOKUP(B2,$E$2:$F$6,2,FALSE)&RIGHT(C2,5)
You can create a reference table and try this formula.
Mar 08 2023 08:55 AM
Mar 08 2023 09:07 AM
Solution=IFERROR(IF(LEN(B2)<3,B2,IF(FIND(" ",B2),LEFT(B2,1)&MID(B2,FIND(" ",B2)+1,1),"")),LEFT(B2,1))&RIGHT(C2,5)
You can try this formula if you want to return the first letter of each word if the MAKE has two words and if you want to return the first letter if the MAKE is one word which is longer than two digits and if you want to return the MAKE if it is one word with up to two digits.
Another alternative could be Flash Fill. In the example you can manually enter "AB44939" in cell D2 and "EW38687" in cell D3. Then the active cell is D4 and you can try Flash Fill with ctrl+E. However with Flash Fill one has to keep in mind that there could be unexpected results especially in case of difficult data structure.
Mar 08 2023 09:27 AM
Mar 08 2023 09:07 AM
Solution=IFERROR(IF(LEN(B2)<3,B2,IF(FIND(" ",B2),LEFT(B2,1)&MID(B2,FIND(" ",B2)+1,1),"")),LEFT(B2,1))&RIGHT(C2,5)
You can try this formula if you want to return the first letter of each word if the MAKE has two words and if you want to return the first letter if the MAKE is one word which is longer than two digits and if you want to return the MAKE if it is one word with up to two digits.
Another alternative could be Flash Fill. In the example you can manually enter "AB44939" in cell D2 and "EW38687" in cell D3. Then the active cell is D4 and you can try Flash Fill with ctrl+E. However with Flash Fill one has to keep in mind that there could be unexpected results especially in case of difficult data structure.