troubleshooting Question

How to take data from one workbook in Excel to another workbook in Excel but putting it in a specific format

Avatar of brasiman
brasiman asked on
Microsoft ExcelSpreadsheetsVB Script
4 Comments2 Solutions480 ViewsLast Modified:
I have a spreadsheet with multiple columns. In each column I have entered data. I need to take that data in each column, and in a different workbook, somehow get that data in an exact format/layout. For example, in column A, it is a date. The date is entered as "0113". On the other workbook, I need that date to show up as 7 characters long, with the 0113 at the end, like 0000113. On the next row down, i have another date "0413". On the other workbook, I need it below 0000113 as 0000413. I have another date below 0413 and need that below 0000413, and so on. Then column B i need the same thing, but it needs to be 10 characters long on the new workbook but I only enter 3 characters in the data. Also, i have a column that is a $. When i enter the data, i put $10 in as 10.00. On the other workbook, i need that to be 15 characters long with 10.00 at the end, however, the decimal can't be there. So it would need to look like 000000000001000. I have lots of columns that need this done. What is the best way to do this? I would appreciate any suggestions. Do i use a macro? Do i just use formulas? I attached an example. Check out Sheet1 and Sheet2. Sheet1 is where i put the data in. Sheet2 is the one i need the data converted to different # of character fields with "0" placed where I didn't put data.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros