Aligning columns of data in a single cell

svjack9

New Member
Joined
Feb 9, 2014
Messages
32
I have a form that I use to collect information regarding pesticide residues in produce. I want to use VBA to create the following string, but I haven't figured out how to line things up so that it looks like everything is aligned. Here is an example of what I'm trying to accomplish in a cell B10 (for example):

Apples: Imidicloprid ............065ppm./100ml
...........Chlorantraniliprole....47ppm.../100ml
...........Fludioxynil...............25ppm.../100ml
(without the dots...)

However, it winds up looking like this:
Apples: Imidicloprid .065 /100ml
Chlorantraniliprole .47 /100ml
Fludioxynil .25ppm /100ml
I've tried:
strComment= me.txtcrop & space(15-len(me.txtcrop)) & me.txtchemical & space(25-len(me.txtchemical) _
& me.txtResidue
Is there a way to create a text string in VBA that creates columns of data?
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
type them in one line and copy paste

remember there are restrictions for the length of a string in a cell

quote
In the versions of Microsoft Excel listed at the beginning of this article, a cell can contain up to 32,767 characters. However, if a cell contains more than 1,024 characters, the following rules apply:


  • Characters after approximately the 1,024th character do not appear in the cell; however, they appear in the formula bar when you edit or select the cell.
  • Although characters after approximately the 1,024th character do not appear, you can detect and manipulate them with worksheet functions (for example, the RIGHT and MID functions) and macro commands (for example the Characters property).
  • If you copy a cell that contains more than 1,024 characters, and then paste them in another cell, all of the characters are pasted into the new cell. However, characters after approximately the 1,024th character do not appear in the destination cell.
  • Characters in a cell after approximately the 1,024th character are not printed and may not appear in print preview.
​unquote
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top