concatenate 3 cells based on condition

**Pasha81** · 11-02-2009, 08:38 AM

Hi guys, need some help again,

In the workbook, 3 columns of importance
Column N,S and AC

Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG

Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.

Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront)

Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.

I have shown the desired solutions for some of the cotracts in the file so it should be easy to understand what i need.

any advice?

thanks lot

**DonkeyOte** · 11-02-2009, 08:42 AM

One approach

AG2: =TEXT($S2,"00")&TEXT($N2,"00")&IF($AC2,"O","F")
copied down

**Pasha81** · 11-02-2009, 08:55 AM

thanks Don, that works beautifully
now one question, working backwards - if i had the solution
e.g 06RSF, how would i delete the 1st 2 digits and the last digit so that only 'RS' remains.

I'll mark it solved as you've alrdy answered my original question

Thanks

**DonkeyOte** · 11-02-2009, 08:56 AM

You can use MID

=MID(string,3,2)

ie extract 2 characters from string starting from character 3.... where string can be a constant or a cell reference to the string in question.