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

alive15

New Member
Joined
Jul 13, 2016
Messages
33
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! :):):)
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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!:LOL:

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

New Member
Joined
Jul 13, 2016
Messages
33
Oh, what an instant gratification society we live in when 3 or 4 clicks is too much work!:LOL:

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.
:oops::mad::(
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

alive15

New Member
Joined
Jul 13, 2016
Messages
33

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here are formulas that would split an entry (in cell A1) in two, based on a comma:
1st entry:
Code:
=LEFT(A1,FIND(",",A1)-1)
2nd entry:
Code:
=MID(A1,FIND(",",A1)+1,LEN(A1))

Is that what you are looking for?
 

alive15

New Member
Joined
Jul 13, 2016
Messages
33
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
    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

New Member
Joined
Jul 13, 2016
Messages
33
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:
Code:
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top