I need to have a certain number of characters in a cell

[jerminski73]

I need to have a certain number of characters in a cell for the data to
import into my scanner. I am using a dbf to import the data to access. The
data all looks good but now I have to export it and have it all a certain
character length to work.... Field 1 6 characters, field 2 30, field 3 8,
field 4 8. I know there is a way to do this but cannot remember where I last
saw it done. Thanks in advance!!!!

 

[Mike H]

Hi,

I'm not sure what you mean by Field 1 etc but have a look at
Data|validation
Text length
and set the validation to the text length your need.

Mike

 

[jerminski73]

Thanks to you both, I need to take random length data and make them all 30
characters. Ideas? I have about 13,000 cells to make this happen to so a
formula would save my sanity!!!

 

[Mike H]

Hi,

That's achievable but you need to be more specific:-

Where are the data? In 1 column? many columns?

Give us some examples of the data

for example if we have ABCD how do we pad this out to thirty characters?
what character/characters do we use?

Mike

 

[ShaneDevenshire]

Hi,

I'm not sure why you are exporting to a DBF file. In Access predefine the
table, hense controlling column width (field size). Then copy the data from
Excel directly into the table or import it using the File, Get External Data
command in Access. This step will be easiest if you name the data range in
Excel (with titles) and save the file before you start the import.

Access won't allow you to bring more characters in than each columns
predefined field sizes allow.

 

[jerminski73]

Sorry, Let me redefine my terms... Field =Column..
Column A 6 characters, Column B 30, Column C 8, Column D 8.
The data that is in my excel sheet is of varying lengths and I need them all
to be the same length to work with my scanner.
My original data is a dbf which I save as xls (less touchy I feel) then
import into access.

 

[Bernd P]

Hello,

If I understand you correctly you want to fill a string up to 30
characters, for example with some blanks?

=LEFT(A1&REPT(CHAR(32),30),30)

Regards,
Bernd

 

[jerminski73]

thats perfect!!!! Thats awesome, Could you explain what some means though?
=LEFT I assume means start at left and fill right to specified characters (30)
(A1&REPT A1 is the cell it is pulling data from and REPT is the function to
repeat
(CHAR(32) is to repeat a certain character, in this case a space (where is a
list of characters?)
,30) is for 30 repeats of the selected character?
30) is for an overall 30 character count of 30?
THANKS AGAIN!

 

[Bernd P]

Hello,

=LEFT(A1&REPT(CHAR(32),30),30)

LEFT is just taking the 30 leftmost characters of whats given to it.

REPT creates a string made of 30 blanks.

You could also take
=LEFT(A1&REPT(" ",30),30)

or

=LEFT(A1&" ",30)

[count up to exactly 30 spaces, please ]

But I thought there might be some room for errors if you try to apply
this.

Have fun,
Bernd