Make the charecters in a cell to equal a certain quanity

[Adam]

Hi

I have a database in excel which holds information for a label program
to import & produce barcodes.

My problem is i need to have each cell to have the same amount of
charecters to keep consistency in the barcode image size, say 10
charecters.

So if i have a label holding the following infomation, ABCDEFG i need
to add 3 spaces to equal the 10 charecters.

Is it poosible to write a macro that counts the charecters and then
add the required amount of spaces to equal 10 charecters.

Thanks

Add

 

[Ron Rosenfeld]

Hi

I have a database in excel which holds information for a label program
to import & produce barcodes.

My problem is i need to have each cell to have the same amount of
charecters to keep consistency in the barcode image size, say 10
charecters.

So if i have a label holding the following infomation, ABCDEFG i need
to add 3 spaces to equal the 10 charecters.

Is it poosible to write a macro that counts the charecters and then
add the required amount of spaces to equal 10 charecters.

Thanks

Add

Simple formula:

=A1&REPT(" ",NumChars-LEN(A1))

and it will give an error if LEN(A1)>NumChars

Or, as a macro:

======================================
Option Explicit
Sub Pad10()
Dim c As Range
For Each c In Selection
c.Value = c.Text & _
Application.WorksheetFunction.Rept(" ", 10 - Len(c.Text))
Next c
End Sub
=====================================
--ron

 

[Rick Rothstein \(MVP - VB\)]

I have a database in excel which holds information for a label program

to import & produce barcodes.

My problem is i need to have each cell to have the same amount of
charecters to keep consistency in the barcode image size, say 10
charecters.

So if i have a label holding the following infomation, ABCDEFG i need
to add 3 spaces to equal the 10 charecters.

Is it poosible to write a macro that counts the charecters and then
add the required amount of spaces to equal 10 charecters.

Select the cells you want to add the spaces to and then run this macro...

Sub Make10CharsWide()
Dim Cel As Range
For Each Cel In Selection
Cel.Value = Format(Cel.Value, "!@@@@@@@@@@")
Next
End Sub

It will add the spaces to the end of the cell's text; if the cell's text is
longer than 10 characters, it will be truncated from the right side down to
10 characters. If you want to add the spaces to the left side, just remove
the exclamation point (!) from the Format function's pattern string;
however, for this condition, if your cell's text is longer than 10
characters, it will not be changed.

Rick