SOLVED

Selecting certain letters and number from different and placing in a separate cell

Copper Contributor

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

4 Replies

@Hathaway7 

=VLOOKUP(B2,$E$2:$F$6,2,FALSE)&RIGHT(C2,5)

You can create a reference table and try this formula.

reference table.JPG 

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
best response confirmed by Hans Vogelaar (MVP)
Solution

@Hathaway7 

=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.

 

make.JPG

 

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.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Hathaway7 

=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.

 

make.JPG

 

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.

View solution in original post