Excel - incluce reformatted number in building a literal text string

Posted on 2011-10-05
Last Modified: 2012-05-12

I have a dollar amount that is being retrived via Vlookup. It contains a decimal. The amount is not more than 9999.99. I need to insert it into a text string with the format of 000000 where the last two positions are the cents (probably obvious but want to be explicit here...). I cannot get it to work - tried creating new intermediate column and different formats for that etc.

Thanks much
Question by:COBOLforever

    Author Comment

    Also - when I vlookup the amount comes over as:
    124.50  vlookup = 124.5
    LVL 26

    Expert Comment

    Hi, COBOLforever.

    Is this the kind of thing you're looking for?

    Brian. No-to-String.xlsm
    LVL 92

    Accepted Solution

    Just structure your VLOOKUP like this:


    Author Closing Comment

    wow. wow. wow. I've been dancing all around that solution for years. So many thanks. really - huge for me. perfect.
    LVL 26

    Expert Comment

    Glad matthewspatrick got you sorted!
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Glad to help :)

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    IF cell equals certain value then display text in cell 4 32
    Oracle Pivot 2 20
    VBA Help TT V-1.1 15 22
    Query question 4 0
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 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

    25 Experts available now in Live!

    Get 1:1 Help Now