Trouble with a function

**sperl** · 04-21-2009, 06:02 PM

Hi everyone. I was recently tasked with an Excel project that I am totally lost on. My director gave me instructions on entering a function, but I keep getting errors on it. Here is the basic story:

I made a spread sheet of contact information formatted like this in each cell:

Company
CEO
Address
City
State
Zip
Phone
Website

I entered 200 of these, but now they say they want each area in it's own column. So instead of being in one cell, now it must go across the sheet. To save time my director gave me these instructions:

Rather than retype the entire list, add the function below to a module in the workbook; it will add a comma at each carriage return. You will then be able to save it as a text file and import it into excel and break the data based on commas. You need to add a comma between the state and zip code.

Please Login or Register to view this content.

To do this easily, you need the list in one column. For this example say they are in column A starting in Cell A1. In cell B1, type the formula below.

Please Login or Register to view this content.

Copy this formula down the entire list and it will remove the carriage return and add commas.

Once you have everything converted, copy the cells and paste as “values” to get rid of the formula and keep the data.

Save the spreadsheet with just the comma delimited data in a txt file.

Import into excel with “comma” as the separator and the text qualifier as “none”. You’ll have the data in columns and can add the headers.

I have very limited exposure to Excel, and I have tried everything I can think of to make this function work but I have not been able to so far. I would REALLY appreciate some advice on this.

Thank you!

**Leith Ross** · 04-21-2009, 08:40 PM

Hello sperl,

Welcome to the Forum!

Here is a faster and much easier way to do this. Copy this macro into your workbook and run it. The directions are below the macro code. Be sure the worksheet you want to convert is the one that is on the screen.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click

the macro's name to Run it.

**sperl** · 04-22-2009, 09:23 AM

Thank you so much for your response!

I am using Excel 2008 for Mac, and I get hung up on step #3. Still trying to figure it out on my own, but no luck so far.

**sperl** · 04-22-2009, 09:33 AM

Oh great. I just found out after some research that Excel 2008 doesn't support Visual Basic anymore. Either I have to find a copy of Excel 2004 for Mac or come up with a workaround.

Any ideas?

**Leith Ross** · 04-22-2009, 10:33 AM

Hello sperl,

The code would have to be rewritten in AppleScript. I don't have a Mac, although I have used them, I have never used AppleScript. I'll contact another moderator who uses Macs to look at your post.

**sperl** · 04-22-2009, 10:36 AM

Thank you so much! I really really really appreciate it.

**Leith Ross** · 04-22-2009, 10:43 AM

Hello sperl,

I sent the him a private message about your problem. Hopefully you will hear back from him today.

**sperl** · 04-22-2009, 10:44 AM

Thanks again!

**mikerickson** · 04-23-2009, 02:37 AM

I still working on the Apple Script for this.

But since its a one time thing, you might try (if your poorly formatted data is in column A)
Put =SUBSTITUTE(A1,CHAR(13),",") in B1. (Your boss is mistaken, the linefeed (for Mac) is CHAR(13) not CHAR(10))
and drag down. Then Copy PasteSpecialValues to change it from formulas to values. Then you can use text to columns (comma delimited) to split out the columns.

**mikerickson** · 04-23-2009, 03:07 AM

Got It! The problem was to Get Value of bRange.

Open Script Editor and paste this into a new window.

Please Login or Register  to view this content.

Run the script and the excel file should be converted.
BTW, if you need more help with Excel 2008/AppleScript, you could PM me.
If you need help with AppleScript, http://macscripter.net/index.php has been very helpful to me.