Formula that inserts letters in front of right function

[rplohocky]

Hello,
I have a cell with 8 digits in it. I am using the "right" function to extract the last 2 digits into another cell. Then I need to add the letters CC on the front of the digits.
Example:
Cell A1 564356-02
Cell B1 CC02
After the formula cell A1 will be 564356.

 

[Rick Rothstein]

Hello,
I have a cell with 8 digits in it. I am using the "right" function to extract the last 2 digits into another cell. Then I need to add the letters CC on the front of the digits.
Example:
Cell A1 564356-02
Cell B1 CC02
After the formula cell A1 will be 564356.

You can only do half of what you want with formulas... while you can put this formula in cell B1 and copy it down as needed...

="CC"&RIGHT(A1,2)

you cannot put a formula into the cells of Column A in order to truncate the values there (a cell can contain either a value or a formula, but not both). There is a VB macro solution, but I think the easiest thing would be to leave Column A as is and put the two values you want from it in Columns B and C. If you do that, here are the formulas to use...

B1: =LEFT(A1,LEN(A1)-3)
C1: ="CC"&RIGHT(A1,2)

If you cannot do this for some reason, write back and we will give you the VB macro code to use instead.

 

[rplohocky]

You can only do half of what you want with formulas... while you can put this formula in cell B1 and copy it down as needed...

="CC"&RIGHT(A1,2)

you cannot put a formula into the cells of Column A in order to truncate the values there (a cell can contain either a value or a formula, but not both). There is a VB macro solution, but I think the easiest thing would be to leave Column A as is and put the two values you want from it in Columns B and C. If you do that, here are the formulas to use...

B1: =LEFT(A1,LEN(A1)-3)
C1: ="CC"&RIGHT(A1,2)

If you cannot do this for some reason, write back and we will give you the VB macro code to use instead.

That did the trick, thank Rick!