How to get a column of data out of excel, and to seperate the items with a comma

I have an Excel document with about 7 columns of data. I need to get one of the columns of data (Lets call it SocialSecurity) out of Excel, put it into a text file, and separate each social security number with a comma. There are about 4000 rows of data, so doing this manually by hand would take too long. So how can I get the column of data out of excel and separate each value with a comma? I can simply highlight a column, and copy it into windows notepad. However I don't know how to separate each social security number with a comma.

Example of data in Excel. Small example involving only 3 records

FirstName | LastName | Age | SocialSecurity
Barry          | Engoli        | 24    | 655987000
Camry        | Boriseli      | 34   | 789776666
Brenda       | Sammy      | 55   | 544667890

End result :

655987000, 789776666, 544667890
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can Concatenate a comma with your SSN in another field and then copy that field into notepad. If you SSN is in column D, you would use =D2 & " , ".


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor Commented:
Hi Flyster. I know practically nothing about using Excel. Your instructions don't make sense to me? Can you give me more detailed instructions?
Christopher KibbleCommented:
Hello, Flyster has you almost there.  Let me expand on what he has to say, and also add the final part that's missing.

First, you need to add the comma to all your social security numbers.  Assuming you have 7 columns of used data, your next free column will be "H".  In H2, enter this formula and press enter:

=D2 & ","

Open in new window

This assumes that your SSN is in the "D" column.  If it's not, change that D to the letter of the column that it's in.  Now, assuming that your H2 column worked right, it should now be the SSN with a comma after it.  Using the little box in the lower right corner of the cell and dragging down until this applies to all of your SSNs all the way down the list.

This should look like my attachment "SSNFormula1.png"

Now you have a list of SSNs with commas, but you want it in a single line list.  To do this, copy the entire column of data, and paste it into work.  Important:  If you're in Word 2007 or greater, you must paste as text.  To do this, drop down the little arrow next to"Paste" on the toolbar, or in the menu, and make sure to pick the "Text" option.  If you can't find it, let us know.

Great, now you have the list, but still with all the line breaks. To remove those, press "CTRL+H" on your keyboard inside of Word to bring up the find and replace box.  In the "Find what" part, enter: ^p (that first character is SHIFT+6 on your keyboard.  It must be a lower case p.  This is saying "Find paragraph markers". Leave the "Replace with" blank.  This means remove them all.  This will look like the attachment WordParagraphReplace1.png.

Now the only thing to do it remove the comma at the very end of your list and you're done!  Good luck!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Please refer to the attached. In column B is the formula that adds the comma to the SSN. You can enter that formula in the first row of your spreadsheet and then copy down. Concatenate joins multiple strings into one.
Glenn RayExcel VBA DeveloperCommented:

1) Open your Excel file
2) Highlight all the values in the SSN column
3) Copy ([Ctrl]+[C])
4) Open MS Word.
5) Paste the values as unformatted text (menu: Home, Paste, PasteSpecial... then select "Unformatted Text")
Word- PasteSpecial - unformatted text6) Use Search and Replace ([Ctrl]+[H]) to convert all Paragraph marks to commas:
Word - Replace paragraph marks with commas
You can then select and copy the updated text as-needed.  Note: you'll have a comma after the last value, so you'll probably want to delete it.  If you want a space after each comma (you didn't specify), just enter a comma and a space in the "Replace with" box instead.

brgdotnetcontractorAuthor Commented:
Awesome. Thank you Experts, I will award the points later this evening.
brgdotnetcontractorAuthor Commented:
I couldn't get the excel formula to work?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.