What is the Basic Formula to Split Cells Using "Text to Columns" Option?

[alive15]

Need to figure out the formula to split cells using "Text to Columns", using the "Delimited" option for Original Data Type,the "Comma" option for Delimiters, and the "General" option for Column Data Format.

Or find faster way to accomplish task without clicking through it.

Thanks!

 

[Joe4]

To use the built-in Macro Recorder to get this code, here is all you have to do:
1. Go to the Macros menu (usually found on the View ribbon)
2. Click on the drop-down box and select "Record Macro"
3. Give it any name you want and click "OK"
4. Perform the steps you want to record manually (in this case, the "Text to Columns" process)
5. Go back to the Macros menu drop-down and select "Stop Recording"
6. Go to the Macros menu drop-down again and select "View Code"
7. Locate the name of the Macro you just recorded, click on it, and select "Edit"

This will show you the VBA code that you just recorded (like I showed in my previous post).
You can copy/paste/edit/move it.

The Macro Recorder is a real handy tool in Excel. Most of us do you have all the arguments of all the VBA functions memorized, so we can use the Macro record to get the code that we need for that.
There are some limitations to the macro recorder, such as:
- it cannot record certain things, like loops
- it is very literal (so if you click on a certain cell address, it will show you clicking that exact cell)
- it records things that you probably don't need (like screen scrolls, etc)

So sometimes you need to clean up your recorded code a little afterwards. Most "ActiveCell" and "Select" statements can be combined or removed. You can also make it more dynamic, so it isn't tied to particular cells.

 

[Scott Huish]

I imagine Joe4 just copied and pasted it. There is an import (and export) however.

You need to right click on the module in the visual basic editor and you can import a .bas file there (which is just a text file with the .bas extension)