?
Solved

Excel - incluce reformatted number in building a literal text string

Posted on 2011-10-05
6
Medium Priority
?
286 Views
Last Modified: 2012-05-12
Hi,

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
0
Comment
Question by:COBOLforever
  • 2
  • 2
  • 2
6 Comments
 

Author Comment

by:COBOLforever
ID: 36921962
Also - when I vlookup the amount comes over as:
124.50  vlookup = 124.5
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36921974
Hi, COBOLforever.

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

Regards,
Brian. No-to-String.xlsm
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36922002
Just structure your VLOOKUP like this:

=TEXT(100*VLOOKUP(A2,LookupRange,2),"000000")
0
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!

 

Author Closing Comment

by:COBOLforever
ID: 36922018
wow. wow. wow. I've been dancing all around that solution for years. So many thanks. really - huge for me. perfect.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36922032
Glad matthewspatrick got you sorted!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36923269
Glad to help :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses

864 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