# Formula that inserts letters in front of right function

#### rplohocky

##### Active Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Rick Rothstein

##### MrExcel MVP
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

##### Active Member
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!

Replies
17
Views
1K
Replies
14
Views
574
Replies
5
Views
659
Replies
4
Views
278
Replies
3
Views
316

1,191,367
Messages
5,986,249
Members
440,013
Latest member
DBB1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back