Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Text in to Columns without text to columns function and more than 3 commas separating it.


Hi, I have searched the web for a solution but can't seem to find one.

I have files which are comma separated, but as I have so many of them I want to set up a sheet which I can paste the data into and it will then put out in a presentable format in another sheet, instead of having to use the Text to columns function. Not sure if this is possible but need an answer.

I have pasted the data in its original format below. All the videos I have seen don't show how to separate data which has any more than 3 commas. The data I get in all comes in in one cell with upto 17 commas.

The first line is a title and the second is a result. All the charcters change so there is never a fixed length between the commas

Hope someone can help, Thanks

4,1,1,Men 200m Heat1,,,,,,,12:11:59.5676 11,3,5,May,Ryan,Northumberland,22.33,,22.33,,,12:11:59.57,,,,22.33,22.33,,

I have posted the updated file



What doess the final version of your data look like? Do you want to extract the strings, like, following the 3rd, fifth and last comma?
Variatus (rep: 4879) May 20, '20 at 8:52 pm
Add to Discussion


Selected Answer

Hello, Dave

Here it is my sugesstion.

I supouse the data are located in column A and start from row 1.

Sub SplitWords()
    n = Application.WorksheetFunction.CountA(Range("A:A"))
    For r = 1 To n
        cadena = Cells(r, 1)
        l = Len(cadena)
        i = 1
        k = 2
            j = i
                letra = Mid(cadena, i, 1)
                i = i + 1
            Loop Until letra = "," Or i > l    
            word = Mid(cadena, j, i - j - 1)
            Cells(r, k) = word
            k = k + 1
        Loop Until i > l
End Sub

I count the amount of rows whit data in column A. Then I take the the of first cell and letter by letter check if is a "," when I find the "," I have the position of the "," whit this I can extract a substring from text (function MID). This processes is repeted until last letter of the text.

Each word I extract from the text is written down in a consecutive cell in same row where the text is.

All this is inside a loop for each cell whit data. I attached the file whit the macro.

Hope this be usefull and can help you.




Hi Basilio, I can't thank you enough, this is exactly what I need you have save me a lot of time,a big headache and money (instead of buying other software).
Thanks so much for your help
Dave1245 (rep: 8) May 21, '20 at 1:40 am
My pleasure!
Basilio (rep: 105) May 21, '20 at 1:43 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login