Formula Question??

[jk_Grand_Accounting]

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.

 

[c_m]

Try:
=SUBSTITUTE(A1,RIGHT(A1,3),"-10")

 

[Georgiboy]

Something like usingn an if...

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

 

[stanleydgromjr]

jk_Grand_Accounting,

Excel Workbook
	A	B
1	060-48000-00	060-48000-10
2	060-48000-01	060-48000-01
3	060-48000-02	060-48000-02
4	060-48000-03	060-48000-03
5	060-48000-00	060-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

 

[jk_Grand_Accounting]

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.

 

[stanleydgromjr]

jk_Grand_Accounting,

Excel Workbook
	A	B
1	060-48000-00	060-48000-10
2	060-48000-01	060-48000-11
3	060-48000-12	060-48000-12
4	060-48000-03	060-48000-13
5	060-48000-10	060-48000-10
Sheet1

Have a great day,
Stan

 

[Peter_SSs]

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.

 

[jk_Grand_Accounting]

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

 

[Peter_SSs]

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)