automated code from 2 cells

[ali32j]

Hi All

I am trying to generate a code based on entries in two cells, desired results as follows:

Name Post code Generated code
ADVANCED ELECTRONICS DY41 9HG ADVELEDY41
ADVANCED ENERGY BX11 1NR ADVENEBX11
ADVANCED THINKING PO18 9RB ADVTHIPO18

I know i can generate a code using =left(A2,3)&left(B2,3) but what i want is to take the first 3 digits of both words in cells A2, for example ADVANCED ELECTRONICS, the code would be ADV + ELE + DY4 (ADVELEDY4) can anyone help with this?

Ali

 

[bluedragon2]

=LEFT(A1,3)&MID(A1,FIND(" ",A1)+1,3)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

That was for the ADVELE, to add the other code:

=LEFT(A1,3)&MID(A1,FIND(" ",A1)+1,3)&LEFT(B1,LEN(B1)-FIND(" ",B1))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

Just an FYI so you can use proper terminology in the future:
This is not "code". This is referred to as a Function or Formula. Code is used to describe a Macro or VBA.

Try this formula:
[tab][COLOR=blue white]=LEFT(A2, 3) & MID(A2, FIND(" ", A2) + 1, 3) & LEFT(B2, 3)[/color]



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ali32j]

Thanks for both your responses, works really well, only issue i have is if a second word is not available i get a #VALUE! response, is there a way to return a result if it is either a 2 word "Name" or 1 word "Name"??

Thanks Ali

 

[bluedragon2]

=LEFT(A1,3)&IF(ISERROR(FIND(" ",A1)),"",MID(A1,FIND(" ",A1)+1,3))&LEFT(B1,4)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...