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!!!!

For Field 1,


Select the Field 1 input cells, and do Data Validation

On the Settings tab, Allow: Custom, Formula: =Len(A1)=6 (change A1 to
reference the first cell in the selection)

On the Error Alert tab, you could have a message like Field must have
six characters!


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27040

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" wrote:

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!!!!

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!!!

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

"jerminski73" wrote:

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!!!

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.


--
Thanks,
Shane Devenshire


"jerminski73" wrote:

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!!!!

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.

"jerminski73" wrote:

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!!!!

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

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!

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