Separate multi-word comma separated cell into rows

I would like to seperate the name of a business and the number attached to the name into 2 columns and then at the comma start a new row in the table:

My input:

{'Business Name One[5],Business Name Two[3],Business Name Three[2]'}

{'Business Name One[5]'}

{'Business Name Two[3],Business Name Three[2]'}

and I need it to output:

'Business Name One' | 5

'Business Name Two' | 3

...

I have tried

unqValue=unique(a.data);

cell_dat_split = cellfun(@(x) sscanf(x, '%s %s %s, '), unqValue, 'Uniform', false);

But its just not doing quite what I want, any help would be great!

Answer 1

[Stephen23]

More efficient than using CELLFUN:

C = {'Business Name One[5],Business Name Two[3],Business Name Three[2]'; ...
    'Business Name One[5]'; ...
    'Business Name Two[3],Business Name Three[2]'};
D = regexp(sprintf('%s,',C{:}),'([^,]+)\[(\d+)\]','tokens');
D = vertcat(D{:})

D = 6×2 cell array
    {'Business Name One'  }    {'5'}
    {'Business Name Two'  }    {'3'}
    {'Business Name Three'}    {'2'}
    {'Business Name One'  }    {'5'}
    {'Business Name Two'  }    {'3'}
    {'Business Name Three'}    {'2'}

[Maddie Long]

Thank you so much!

Answer 2

[Peter Perkins]

[Edited to be even modern by using split, not strsplit, join, not strjoin, and double, not str2double.]

Here's a more modern version, using string and patterns, which are really the best way to go these days. There's a bit of trickiness at the beginning, going from ragged cellstr to string, but after that it's smooth sailing.

C = {'Business Name One[5],Business Name Two[3],Business Name Three[2]';
     'Business Name One[5]';
     'Business Name Two[3],Business Name Three[2]'}

C = 3×1 cell array
    {'Business Name One[5],Business Name Two[3],Business Name Three[2]'}
    {'Business Name One[5]'                                            }
    {'Business Name Two[3],Business Name Three[2]'                     }

Turn that "ragged" cell array into one long string, then split at the commas.

S = join(string(C),',')

S = "Business Name One[5],Business Name Two[3],Business Name Three[2],Business Name One[5],Business Name Two[3],Business Name Three[2]"

S = split(S,","); S = S(:)

S = 6×1 string array
    "Business Name One[5]"
    "Business Name Two[3]"
    "Business Name Three[2]"
    "Business Name One[5]"
    "Business Name Two[3]"
    "Business Name Three[2]"

Now it's just a matter of pulling out the text and the numbers. Pattern is much easier to use than regexp.

pat = "[" + digitsPattern(1) + "]";
biz = extractBefore(S,pat)

biz = 6×1 string array
    "Business Name One"
    "Business Name Two"
    "Business Name Three"
    "Business Name One"
    "Business Name Two"
    "Business Name Three"

buz = extract(S,digitsPattern)

buz = 6×1 string array
    "5"
    "3"
    "2"
    "5"
    "3"
    "2"

Now convert them to more useful types and put them in a table.

biz = categorical(biz);
buz = double(buz);
t = table(biz,buz)

t = 6×2 table
            biz            buz
    ___________________    ___

    Business Name One       5 
    Business Name Two       3 
    Business Name Three     2 
    Business Name One       5 
    Business Name Two       3 
    Business Name Three     2 

Answer 3

[Voss]

C = { ...
    'Business Name One[5],Business Name Two[3],Business Name Three[2]'; ...
    'Business Name One[5]'; ...
    'Business Name Two[3],Business Name Three[2]'; ...
    }

C = 3×1 cell array
    {'Business Name One[5],Business Name Two[3],Business Name Three[2]'}
    {'Business Name One[5]'                                            }
    {'Business Name Two[3],Business Name Three[2]'                     }

C = cellfun(@(x)strsplit(x,','),C,'UniformOutput',false);
C = [C{:}].'

C = 6×1 cell array
    {'Business Name One[5]'  }
    {'Business Name Two[3]'  }
    {'Business Name Three[2]'}
    {'Business Name One[5]'  }
    {'Business Name Two[3]'  }
    {'Business Name Three[2]'}

C = regexp(C,'([^\[]+)\[(\d+)\]','tokens');
C = [C{:}]

C = 1×6 cell array
    {1×2 cell}    {1×2 cell}    {1×2 cell}    {1×2 cell}    {1×2 cell}    {1×2 cell}

C = vertcat(C{:})

C = 6×2 cell array
    {'Business Name One'  }    {'5'}
    {'Business Name Two'  }    {'3'}
    {'Business Name Three'}    {'2'}
    {'Business Name One'  }    {'5'}
    {'Business Name Two'  }    {'3'}
    {'Business Name Three'}    {'2'}

[Maddie Long]

Thank you so much! This is perfect!

[Voss]

You're welcome! Not quite perfect, but just ok.