Separate multi-word comma separated cell into rows

8 views (last 30 days)
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!

Accepted Answer

Stephen23
Stephen23 on 15 Mar 2022
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'}

More Answers (2)

Peter Perkins
Peter Perkins on 17 Mar 2022
Edited: Peter Perkins on 17 Mar 2022
[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

Voss
Voss on 15 Mar 2022
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'}

Categories

Find more on Characters and Strings in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!