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]

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

Oh, what an instant gratification society we live in when 3 or 4 clicks is too much work!

If you are looking to automate the task, that is what Macros and VBA are for. If you turn on your Macro Recorder and record yourself performing these steps manually, you will get most of the VBA code that you need.
You can usually make the code dynamic with a few small edits, if desired.
And you can assign a keyboard shortcut or button to run the macro without having to go to the Macro menu.

 

[alive15]

Oh, what an instant gratification society we live in when 3 or 4 clicks is too much work!

If you are looking to automate the task, that is what Macros and VBA are for. If you turn on your Macro Recorder and record yourself performing these steps manually, you will get most of the VBA code that you need.
You can usually make the code dynamic with a few small edits, if desired.
And you can assign a keyboard shortcut or button to run the macro without having to go to the Macro menu.

No, unfortunately we don't have a person to stand around and do this for every single part we run through our machine, 24/7. I'm already using a macro and I've already recorded the task, but just looking for ways to do it faster.

I honestly found your comment super annoying, but thanks for reply anyways.

 

[Joe4]

I honestly found your comment super annoying, but thanks for reply anyways.

Hmm... I guess the laughing icon didn't successfully relate that I was just joking around (got to have a little fun at work, or else the stress will kill you!)
Note that I did address your question (I wasn't just making a flippant post and not trying to help you).

I'm already using a macro and I've already recorded the task, but just looking for ways to do it faster.

I am not sure I understand what you are looking for here. Using Macros/VBA is the fastest way. You may even be able use special VBA called "Event Procedures" which is VBA code that runs automatically upon some event happening (such as the opening of a file, the change of a cell, the saving of a file, etc).

So, you could schedule your code to run on a single click of a button, a keyboard shortcut, or perhaps even automatically. What could be faster than that?

I am guessing that there is more to this question here than meets the eye. If you can give us more detail, what the issues you are currently having with trying to incorporate it into your VBA code here, we may be able to help you with that.

 

[alive15]

Hmm... I guess the laughing icon didn't successfully relate that I was just joking around (got to have a little fun at work, or else the stress will kill you!)
Note that I did address your question (I wasn't just making a flippant post and not trying to help you).


I am not sure I understand what you are looking for here. Using Macros/VBA is the fastest way. You may even be able use special VBA called "Event Procedures" which is VBA code that runs automatically upon some event happening (such as the opening of a file, the change of a cell, the saving of a file, etc).

So, you could schedule your code to run on a single click of a button, a keyboard shortcut, or perhaps even automatically. What could be faster than that?

I am guessing that there is more to this question here than meets the eye. If you can give us more detail, what the issues you are currently having with trying to incorporate it into your VBA code here, we may be able to help you with that.

Shoot, my bad, I thought you were laughing at me for being lazy, lol.

So I'm already using the macro recorder to "click through" and split the column into two. I want to find a formula version and see if this is faster. For example, instead of highlighting five cells and "clicking" the sum button, I can type "=sum(A1:A5)" and add everything together with the keyboard macro.

In my case, I am looking to type out a formula that can split my column into two. If this exists, then I can use my keyboard macro to type out the code and execute faster than clicking through.

I really don't need to make anything faster since the clicking option works great, I only want to learn the formula if it exists and see if it runs faster than clicking through.

Thanks!

 

[Jonmo1]

I can't imagine a formula being any faster than Text To Columns (even if the text to columns is applied through VBA).
You keep saying 'clicking through'
Can you post the code you currently have that does the 'clicking through'
Perhaps the text to columns can be improved by not actually 'selecting' cells and ranges.

 

[Joe4]

Here are formulas that would split an entry (in cell A1) in two, based on a comma:
1st entry:

=LEFT(A1,FIND(",",A1)-1)

2nd entry:

=MID(A1,FIND(",",A1)+1,LEN(A1))

Is that what you are looking for?

 

[alive15]

I'm using "JitBit Macro Recorder" to record Mouse movements and Keyboard movements. The software automatically records my clicks and whatever I type, so if I have the formula, I can just paste it and execute.

Unfortunately, neither of those codes works exactly same way as the Text-to-Column.

I don't believe a formula exists to work the way I want unfortunately.

I'll just stick with the clicking macro. Thanks for the help though.

 

[Joe4]

If you use the built-in Macro Recorder that comes with Excel, you can record your actual selection for Text-to-Column. So the code would actually do the "Text to Columns" for you, exactly as if you would have done it manually.

So, if I wanted to perform a Text to Columns on column A, using comma-delimited, the recorded code looks like this:

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

If that does not do what you want, please explain how it is not doing what you want.

 

[alive15]

If you use the built-in Macro Recorder that comes with Excel, you can record your actual selection for Text-to-Column. So the code would actually do the "Text to Columns" for you, exactly as if you would have done it manually.

So, if I wanted to perform a Text to Columns on column A, using comma-delimited, the recorded code looks like this:

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

If that does not do what you want, please explain how it is not doing what you want.

This is what I am looking for; how did you export the recorded code like that? Is there a way to export / import this? I did not see an export macro or import macro button.