excel formula reqd for renumbering of ids

Posted on 2011-05-02
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.

Question by:FrankSasso
    LVL 81

    Accepted Solution

    Column B:


    Column C:


    LVL 50

    Expert Comment


    in B1


    In C1


    cheers, teylyn
    LVL 50

    Expert Comment

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

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

    Author Comment

    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?
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    It will work if it refers to column B and not column A:

    Column C:



    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Vlookup for IP 3 34
    Excel Max 6 24
    Adding Data To Master File From Import Final 9 19
    Tricky lookup and concatenate formula 6 30
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now