Full Version: Aligning Data
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
jmarsh1
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.

RCV $10,000
Depreciation $15,000
ACV $3,000

This is how I would like it to look:

RCV $10,000
Depreciation $15,000
ACV $3,000

Is there a way to make this possible?
Thank you for your time.
jmarsh1
Forgot to mention, I used this VB code for the copy function. Thanks.

Private Sub CommandButton1_Click()
Range("b2:b24,d2:d24").Copy
End Sub
dflak
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.
jmarsh1
Thanks for the reply. I see my mistake. How I want it to paste is like this:

RCV $10,000
Depreciation $15,000
ACV $3,000

Basically I would like the dollar amounts all to line up.

Thank you!
jmarsh1
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.
dflak
I asssume that the description and the dollar amounts are two separate columns in Excel. To what application are you pasting this data?

I 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.

QUOTE

RCV $10,000
Depreciation $15,000
ACV $3,000
dflak
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.



jmarsh1
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.