Fixed Character (alpha) length

[Seth]

I am importing a CSV file that contains fields with
required character lengths when used with the parent
application. I would like to use Excel to edit this file,
however I am unable to enable a fixed Character Length for
the cells.
Example: The database file (Exported to .csv) has a
customer name 'Smith John' as one of the comma separated
values. This name is ten characters is length (includign
the space). However, the database file reserves twenty
spaces for each customer name. So 'Smith John'(10 spaces)
is actually 'Smith John '(20 spaces).
Excel removes/ignores these extra spaces when imported,
thus they are no longer allied when I export back to a CSV
file.

Is there a way to force/fix the character length of a cell?
Thank you in advance for your time.

 

[Peter Atherton]

Is there a way to force/fix the character length of a cell?
Thank you in advance for your time.
.

Seth

You can select the names and run this macro to insert the
spaces

Sub test()
Dim l As Integer, i As Integer, n As Integer
Dim c As Variant, temp As Variant
Dim x As String
For Each c In Selection
l = Len(c)
If Len(c) < 20 Then
temp = c
n = 20 - l
x = " "
For i = 1 To n
temp = temp & x
Next
c.Value = temp
Else: c.Value = c
End If
Next c

End Sub

(e-mail address removed)

Regards
peter

 

[Dave Peterson]

First, it doesn't sound like a CSV file. It sounds like a fixed width file.
You wouldn't need separators if you know exactly how wide each field should be.

====

You have a few choices (try against a copy of your worksheet):

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]

 

[RagDyer]

One idea!

Could you add a "helper" column, add the necessary spaces to this helper"
column, then eliminate the formula, leaving the existing text and spaces
behind, and then delete the original.

Text in column A.
Length necessary for export = 20.

Enter this in B1 and copy down as needed:

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

To remove the formula, and leave just the data and spaces behind, you should
now, select the data in column B,
Right click in the selection and choose "Copy",
Right click again and choose "PasteSpecial",
Check "Values", then <OK>.

You now have column A with your original data, and column B with your data
plus the necessary spaces appended to it to make up 20 places.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I am importing a CSV file that contains fields with
required character lengths when used with the parent
application. I would like to use Excel to edit this file,
however I am unable to enable a fixed Character Length for
the cells.
Example: The database file (Exported to .csv) has a
customer name 'Smith John' as one of the comma separated
values. This name is ten characters is length (includign
the space). However, the database file reserves twenty
spaces for each customer name. So 'Smith John'(10 spaces)
is actually 'Smith John '(20 spaces).
Excel removes/ignores these extra spaces when imported,
thus they are no longer allied when I export back to a CSV
file.

Is there a way to force/fix the character length of a cell?
Thank you in advance for your time.

 

[Earl Kiosterud]

Seth,

I've heard of folks having success using the prn file format (Save As, File
type box). You need to adjust the column widths to get the count of
characters you need for each field, and should use a non-proportional font,
like Courier. This doesn't work for records over somewhere around 240
characters.

 

[Seth]

Thank you, Thank you, Thank you!