I need to count the number of instances of a comma (,) in a

[bab01824]

I have a series of cells in column D with contents such as brkt123, nhma~3456, slm~4347,lgca~23456. I need to be able to put the substrings between commas into their own cells across a row. The length of each substring can vary, as well as the number of substrings.

My unelegant solution was to use LEFT to find the first one, and search within LEFT to identify the number of characters.
=LEFT(D1,SEARCH(",",D1)-1)

To find the second substring, I used MID like this;
=MID($D1,SEARCH(",",$D1,E1)+1,G1-E1-1)
where E1 is the result of SEARCH(",",D1), and G1 is the result of
=SEARCH(",",$D1,E1+1)

This works, but has a couple of faults or inefficiencies;
1) it requires a column (E1 & G1 in my example) just to store the position of the next comma, and
2) It does not work for the last substring in D1

Any advice?

 

[PaddyD]

highlight the column, go to data | text to columns, select delimited, hit next, select coma, hit finish.

 

[bab01824]

Re: I need to count the number of instances of a comma (,) i

Thanks Paddy. It must be too late for me to think clearly!