Solved

excel formula reqd for renumbering of ids

Posted on 2011-05-02
Medium Priority
212 Views
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
0
Question by:Frank .S
• 2
• 2

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 35510275
Column B:

=REPLACE(A1,5,4,"999.")

Column C:

=MID(B1,5,8)

Kevin
0

LVL 50

Expert Comment

ID: 35510279
Hello,

in B1

=SUBSTITUTE(A1,"000","999")

In C1

=SUBSTITUTE(RIGHT(B1,8),"-",".")

cheers, teylyn
0

LVL 50

Expert Comment

ID: 35510285
correction (did not see the dash is replaced with a dot in column B already

B1 =SUBSTITUTE(A1,"000-","999.")
C1 =RIGHT(B1,8)
0

Author Comment

ID: 35510293
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?
0

LVL 81

Expert Comment

ID: 35510299
It will work if it refers to column B and not column A:

Column C:

=MID(B1,5,8)

Kevin
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll