Add a character in .cd file

G

Guest

I have a comma delimited file that opens up in Excel.
I need to add a quote character in the beginning of one cell.
Sample of what it looks like now: (all info is one cell such as A1)

this is in Cell A1
company name,"address","","city","state","zip"
this is in Cell A2
company name,"address","","city","state","zip"

I need it to look like this
Cell A1
"company name","address","","city","state","zip"
Cell A2
"company name","address","","city","state","zip"

Please note that the file has 2500 lines.
 
G

Guest

Hi Jeffrey. I used three helper columns just because it makes the fomulas
easier (for me), but they could all be entered in one column. Assuming your
data starts in A2, in B2 enter:=Search(",",A2,1). This will find the
character number of the first comma - after Company Name. In C2 enter: =
Len(A2). This gives you the total characters in the cell. In D2 enter:
=""""&Left(A2,B2-1)&""""&Right(A2,C2-B2+1). This will give you single quotes
around Company Name while maintaining the rest of the data. Copy cells B2-D2
all the way down - 2,500 times. Then Copy D2 - D2500 and paste
special-values on top of A2. You can then delete columns B,C and D. HTH.
 
P

Pete_UK

As long as you have no commas in the company name, you could try this:

=CHAR(34)&LEFT(A1,SEARCH(",",A1)-1)&CHAR(34)&MID(A1,SEARCH(",",A1),255)

Copy the formula down for as many entries as you have in column A. Then
highlight all the cells with this formula in and fix the values
(<copy>, Edit | Paste Special | Values (check) | OK then <Enter>). You
could then delete column A.

Hope this helps.

Pete
 
G

Guest

I am a novice on some or most of Excel commands, so I do not have an idea as
to how to do what you are asking.
I wish there was a way to attach a partial file that i am having trouble with.
All of the info for each company in in one cell.
 
P

Pete_UK

Jeffrey,

if your data is currently in cell A1, then you need to select cell B1
and type the formula in as it appears, starting with the equals sign. A
quicker and less error-prone way is if you copy it from the newsgroups
or email (highlight the complete formula, then do CTRL-C) and then
paste it into the cell. If you have done this correctly, then you
should see your first data item with quotes around the company name in
cell B1.

With B1 selected, double-click the fill-icon - this is the small black
square in the bottom right corner of the cursor - and this will copy
your formula down to the bottom of the data in column A, i.e. to B2500
or thereabouts, if your data is in one continuous list.

With all the cells B1:B2500 highlighted, then follow this procedure:
click <copy> (you will get the dotted lines around the highlighted
block), then click Edit, followed by Paste Special, then click against
the Values button, and then click OK, and finally press the <Enter>
key. This will have fixed the values in column B, so they do not depend
on the contents of column A, and so you can highlight column A by
clicking the A at the top of the column and then Edit | Delete to get
rid of that column. This will leave you with what you want.

Hope this helps.

Pete
 
G

Guest

That worked thanks for your help.

Pete_UK said:
Jeffrey,

if your data is currently in cell A1, then you need to select cell B1
and type the formula in as it appears, starting with the equals sign. A
quicker and less error-prone way is if you copy it from the newsgroups
or email (highlight the complete formula, then do CTRL-C) and then
paste it into the cell. If you have done this correctly, then you
should see your first data item with quotes around the company name in
cell B1.

With B1 selected, double-click the fill-icon - this is the small black
square in the bottom right corner of the cursor - and this will copy
your formula down to the bottom of the data in column A, i.e. to B2500
or thereabouts, if your data is in one continuous list.

With all the cells B1:B2500 highlighted, then follow this procedure:
click <copy> (you will get the dotted lines around the highlighted
block), then click Edit, followed by Paste Special, then click against
the Values button, and then click OK, and finally press the <Enter>
key. This will have fixed the values in column B, so they do not depend
on the contents of column A, and so you can highlight column A by
clicking the A at the top of the column and then Edit | Delete to get
rid of that column. This will leave you with what you want.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top