Formatting query adding , & " "

[Louise]

Using Excel 2002. I have a column list of 3000 items. They are of
varying lengths the maximum being 11 characters.
I would like to take this list & be able to change it from vertical to
horizontal.
I would like to add a comma in between each entry with no extra spaces.
I would like to add " " around each entry, but the quotes must all be
in the same fixed space.
ex: I have an entry that is 11 characters long. it should have quotes.
I also have an entry that is 8 characters long, the quotes on this
shorter entry must be in the same place as if it were an 11 character
entry.

"ABRDOH79 ","AKRNOH25DC1", etc...

After this is done I need to save it as a text file so that I can copy
& paste it to another location.
#1 is this possible?
#2 can someone please tell me how?
Thank You!

 

[Guest]

Louise,

I don't know about creating the horizontal comma deliminated file directly in Excel, but you can use the following to make all of your data into 11 characters.

=A1&LEFT(" ",11-LEN(A1))

or this to get the quotes:

=""""&A1&LEFT(" ",11-LEN(A1))&""""

I would expect that you won't need to use the quotes in your excel file. When converting to a text file they are ussually added automatically.

If you convert your data with the first formula, you can then Copy and Paste_Special>Values to make them permanent. You can then delete the original column. If you save this file as Text (OS/2 or MS-DOS) (*.txt) you will get pretty close to what you need.

You could then open the text file in WORD. Do a Edit>Replace. For Find_What, hit the More button and then the special button and select Paragraph mark. For Replace_With enter ",". then you only need to fix the very first and very last characters to get what you need.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Louise > wrote: -----

Using Excel 2002. I have a column list of 3000 items. They are of
varying lengths the maximum being 11 characters.
I would like to take this list & be able to change it from vertical to
horizontal.
I would like to add a comma in between each entry with no extra spaces.
I would like to add " " around each entry, but the quotes must all be
in the same fixed space.
ex: I have an entry that is 11 characters long. it should have quotes.
I also have an entry that is 8 characters long, the quotes on this
shorter entry must be in the same place as if it were an 11 character
entry.

"ABRDOH79 ","AKRNOH25DC1", etc...

After this is done I need to save it as a text file so that I can copy
& paste it to another location.
#1 is this possible?
#2 can someone please tell me how?
Thank You!

 

[J Donahue]

First, to get the file from vertical to horizontal you
need to highlight the range then do Edit->Copy

Then you can Edit->Paste Special and click on Transpose
and this will change them to horizontal.

What I would recommend for adding the quotes around items
and then the commas would be:

Before you transpose the column:

Use the Concatenate formula to add in the items you need
for the quotes you may need to use two of the ' symbols
but it should work correctly once you copy it to a text
file.

A1 B1

Data =CONCATENATE("''",A1,"'',")

This should produce "Data", in cell B1. Then you
can transpose the B column and it should do what you need.

Hope this helps

jeff

 

[Guest]

Allan,
Louise's file has over 3000 entries. There aren't enough columns to transpose.

Regards,
Mark Graesser

----- Allan wrote: -----

-----Original Message-----
Louise,

deliminated file directly in Excel, but you can use the
following to make all of your data into 11 characters.your excel file. When converting to a text file they are
ussually added automatically.then Copy and Paste_Special>Values to make them
permanent. You can then delete the original column. If
you save this file as Text (OS/2 or MS-DOS) (*.txt) you
will get pretty close to what you need.Edit>Replace. For Find_What, hit the More button and then
the special button and select Paragraph mark. For
Replace_With enter ",". then you only need to fix the
very first and very last characters to get what you need.

Mark Graesser
(e-mail address removed) items. They are of
varying lengths the maximum being 11 characters.
I would like to take this list & be able to change it from vertical to
horizontal.
I would like to add a comma in between each entry with no extra spaces.
I would like to add " " around each entry, but the quotes must all be
in the same fixed space.
ex: I have an entry that is 11 characters long. it should have quotes.
I also have an entry that is 8 characters long, the quotes on this
shorter entry must be in the same place as if it were an 11 character
entry. so that I can copy
& paste it to another location.
#1 is this possible?
#2 can someone please tell me how?
Thank You!
To convert from vertical to horizontal:

Copy --> Paste Special --> Transpose
Then, below each cell the relative formula should be:
=""""&A1&LEFT(" ",11-LEN(A1))&""","
It seems to me that this is extremely redundant. Either a
text file is fixed length, comma delimited or uses some
other delimiter (such as quotes). You are using all three.

You may choose to NOT include the comma in the formula,
then save the file in a *.csv format. Upon opening the
file in another application, such as Notepad, you will
find that Excel placed the commas in there for you.

Allan

 

[Louise]

Hi Mark,

I now know why I couldn't transpose horizontally, I was trying t
paste/special too many items. I can break it up into multiple rows-n
problem with that.

Thank you for your reply. I am a regular user of Excel, but no expert.
I am a little confused by the instructions you gave.
The formula you gave me-Where does it go?

I mean, if I have my horizontal data in a1, b1, c1,etc.. am I t
click
on a1 & then add that formula?
Does that sound dumb?

What it is,is a vertical list of 3000 abbreviations that range betwee
7 & 11 characters. I need to transpose it horizontally, & eac
abbreviation needs to have a comma after it & each abbrev. needs t
have quotes around it. But, the end quote must always be in the 12t
space. I am then going to save it as a text file keeping that format s
I can add it to a query program we use here at work. If there were jus
a few, I would do it manually, but there are I think 3109 of them.
Did that make any sense?
Please let me know it the attachment can be opened

Attachment filename: quotes & commas.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=40156

 

[Dave Peterson]

Another way is to use a macro:

Option Explicit
Sub testme()

Dim myFile As String
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

myFile = "C:\list.txt"

With ActiveSheet
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Close #1
Open myFile For Output As #1
For iRow = FirstRow To LastRow
Print #1, Chr(34) & Left(.Cells(iRow, "A").Value _
& Space(11), 11) & Chr(34);
If iRow = LastRow Then
Print #1, vbCrLf;
Else
Print #1, ",";
End If
Next iRow
Close #1
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm