Forum Discussion
Solved
Selecting certain letters and number from different and placing in a separate cell
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
=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.
=VLOOKUP(B2,$E$2:$F$6,2,FALSE)&RIGHT(C2,5)You can create a reference table and try this formula.
- Thanks for your reply. Is there an easier than create a reference table. I do remember a long time ago being able to do this task but I do recall creating a reference table.
Thanks=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.
