# Formula Question??

#### jk_Grand_Accounting

##### New Member
I have a bunch of cells with something like this in it: 060-48000-00 & I need a formula that will make excel return 060-48000-10

I need the formula to change the -00 to -10 for each instance.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try:
=SUBSTITUTE(A1,RIGHT(A1,3),"-10")

Something like usingn an if...

=IF(RIGHT(A1,2)="00",LEFT(A1,FIND("-",A1,5))&"10","")

jk_Grand_Accounting,

Excel Workbook
AB
1060-48000-00060-48000-10
2060-48000-01060-48000-01
3060-48000-02060-48000-02
4060-48000-03060-48000-03
5060-48000-00060-48000-10
Sheet1

The formula in cell B1 copied down:
=IF(RIGHT(A1,3)="-00",LEFT(A1,9)&"-10",A1)

Have a great day,
Stan

Thanks that formula works

but what if I want the formula to just change the first number after the last dash - to a 1 & keep the last number after that exactly as the same no matter what number.

jk_Grand_Accounting,

Excel Workbook
AB
1060-48000-00060-48000-10
2060-48000-01060-48000-11
3060-48000-12060-48000-12
4060-48000-03060-48000-13
5060-48000-10060-48000-10
Sheet1

Have a great day,
Stan

Thanks that formula works

but what if I want the formula to just change the first number after the last dash - to a 1 & keep the last number after that exactly as the same no matter what number.
If your question has not been answered, could you please give some sample data (say 8 or 10 examples) together with the expected results?

If your data format varies at all, please make sure you include a variety of possibilities so that a good, concise solution can be suggested.

That formula worked.....THANKS for your help!!!!!

That formula worked.....THANKS for your help!!!!!
Again, because we haven't seen much sample data, this is a bit of a guess but from what I can see, this should do the same thing as the last suggestion:

=REPLACE(A1,11,1,1)

Replies
3
Views
269
Replies
1
Views
172
Replies
1
Views
331
Replies
8
Views
231
Replies
2
Views
753

1,196,259
Messages
6,014,290
Members
441,813
Latest member
elevate

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