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

0

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

Answer
Discuss

Discussion

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: 3738) May 20, '20 at 8:52 pm
Add to Discussion

Answers

1
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
        Do
            j = i
            Do
                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
    Next    
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.

Regards
Basilio  

Discuss

Discussion

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
Dave
Dave1245 (rep: 6) May 21, '20 at 1:40 am
My pleasure!
Basilio (rep: 65) 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