?
Solved

excel formula reqd for renumbering of ids

Posted on 2011-05-02
5
Medium Priority
?
212 Views
Last Modified: 2012-05-11
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
Comment
Question by:Frank .S
  • 2
  • 2
5 Comments
 
LVL 81

Accepted Solution

by:
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
ID: 35510279
Hello,

in B1

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


In C1

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

cheers, teylyn
0
 
LVL 50
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

by:Frank .S
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

by:zorvek (Kevin Jones)
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question