excel formula reqd for renumbering of ids
I have in excell wkbk with 1 column which has the following info.
col A
Row1 200.000-0010,
in cell B1 i need to change the 3 middle numbers (000) to 999 so it reads 200.999.0010, and then in cell C1, i want to renumber the id to "999.0010
I have over 100 items that i need to use these 2 formulas for so please assist.
2
3
col A
Row1 200.000-0010,
in cell B1 i need to change the 3 middle numbers (000) to 999 so it reads 200.999.0010, and then in cell C1, i want to renumber the id to "999.0010
I have over 100 items that i need to use these 2 formulas for so please assist.
2
3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
correction (did not see the dash is replaced with a dot in column B already
B1 =SUBSTITUTE(A1,"000-","999
C1 =RIGHT(B1,8)
B1 =SUBSTITUTE(A1,"000-","999
C1 =RIGHT(B1,8)
ASKER
hi zorvek thankyou for your help, the 1st one is correct however the 2nd shows a dash (-), i want it to show a (.) fullstop between the 999.0010, doe your formula need to adjusted?
It will work if it refers to column B and not column A:
Column C:
=MID(B1,5,8)
Kevin
Column C:
=MID(B1,5,8)
Kevin
in B1
=SUBSTITUTE(A1,"000","999"
In C1
=SUBSTITUTE(RIGHT(B1,8),"-
cheers, teylyn