Feb 20 2012, 11:33 AM
Hello, I have a copy and paste feature in excel that allows users to paste data into another program. The only problem is when it is pasted over, it does not align up properly. Here is an example.
This is how I would like it to look:
Is there a way to make this possible?
Thank you for your time.
Feb 20 2012, 12:03 PM
Forgot to mention, I used this VB code for the copy function. Thanks.
Private Sub CommandButton1_Click()
Feb 20 2012, 04:03 PM
It's difficult to tell what you want considering that the forum software removes extra spaces in text. So in your original post, it looks like you are getting exactly what you want.
Try wrapping the examples in [quote ][ /quote ] tags to preserve the spacing.
One suggestion is testing for the length of the string representing the dollar amount and padding with the appropriate number of characters - however, this will only work if you use a non-proportional font.
You could also try putting a tab character between the text and the dollar amount but that will depend on how that would be interpreted in the application you paste to. This might work if you are pasting into an application such as Word.
Feb 21 2012, 10:29 AM
Thanks for the reply. I see my mistake. How I want it to paste is like this:
Basically I would like the dollar amounts all to line up.
Feb 21 2012, 10:31 AM
For some reason, when I submit my post, it does not keep the dollar figures lined up. That's all i'm trying to do. Thank you.
Feb 21 2012, 11:10 AM
I asssume that the description and the dollar amounts are two separate columns in Excel. To what application are you pasting this data?
tried quoting the amounts as I suggested (see below). It didn't work. If you are pasting into an HTML application you will probably have to generate table tags.
Feb 21 2012, 11:49 AM
Here is one implementation of it. You have to decide how big a string you want. I chose 30: 20 characters for the item and 10 for the dollar figure. Then you have to pad both with blanks to equal this length. This means you will need to know the length of each.
The item is easy enough: LEN(ITEM).
However the dollar value is trickier. LEN($1,000,000) is 7 (assuming that the value is numeric and not a string). However what we want to see is 10 characters (7 numbers, two commas and a dollar sign). The following formula gives the corrected length: =LEN(Dollar Value)+INT(LOG10(Dollar Value)/3)+1
Other formulas used are REPT which simply repeats the specified character the indicated amount of times and TEXT to reconsitutue the dollar sign and commas.
The attached spreadsheet has this calculated out two ways:
-- Rows 2:4 uses helper columns that allow you to see each step
-- Rows 8:10 shows the helper column information condensed into a single formula
Note that I am using currier font which is non-proprotional - each character takes up the same amount of space.
Feb 21 2012, 12:28 PM
Yes, two separate columns. The data is pasted into a claims handling environment. Access database I believe. I will give your idea a try. Thank you.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here