Forum Discussion
Selecting certain letters and number from different and placing in a separate cell
=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.
- Thank you
