advanced searching or sorting through jumbled data

[AleinPump]

Hi team,

I am jammed with a situation and I was hoping that somebody can help me out.

I gave a team a sheet to fill out, where they had to copy a row with 3 categories, located in 3 columns, and asked them to copy+paste those 3 categories next to the other input I was requesting.
Unfortunately, most pasted all kinds of versions of those 3 columns jumbled into 1 cell only, for the most part by combining the categories in different ways. Some used tabs as text (not sure if Excel sees those as tabs or a bunch of spaces), others used the ">" character, some used "/"'s and " "'s...

Essentially I am trying to find a way to count how many times the combination A2, B2, and C3 was used in column E:

	A	B	C	D	E
1​	Cat 1	Cat 2	Cat 3		examples of jumbled input
2​	blah 1	qwerty 1	asdf 1		blah 1qwerty 1asdf 1
3​	blah 2	qwerty 2	asdf 2		blah 2qwerty 2asdf 2
4​	blah 3	qwerty 3	asdf 3		blah 3qwerty 3asdf 3
5​	blah 4	qwerty 4	asdf 4		blah 4qwerty 4asdf 4
6​	blah 5	qwerty 5	asdf 5		blah 5qwerty 5asdf 5
7​	blah 6	qwerty 6	asdf 6		blah 6>qwerty 6>asdf 6
8​	blah 7	qwerty 7	asdf 7		blah 7>qwerty 7>asdf 7
9​	blah 8	qwerty 8	asdf 8		blah 8>qwerty 8>asdf 8
10​	blah 9	qwerty 9	asdf 9		blah 9>qwerty 9>asdf 9
11​	blah 10	qwerty 10	asdf 10		blah 10>qwerty 10>asdf 10
12​	blah 11	qwerty 11	asdf 11		blah 11/qwerty 11/asdf 11
13​	blah 12	qwerty 12	asdf 12		blah 12/qwerty 12/asdf 12
14​	blah 13	qwerty 13	asdf 13		blah 13/qwerty 13/asdf 13
15​	blah 14	qwerty 14	asdf 14		blah 14/qwerty 14/asdf 14
16​	blah 15	qwerty 15	asdf 15		blah 15/qwerty 15/asdf 15
17​	blah 16	qwerty 16	asdf 16		blah 16 qwerty 16 asdf 16
18​	blah 17	qwerty 17	asdf 17		blah 17 qwerty 17 asdf 17
19​	blah 18	qwerty 18	asdf 18		blah 18 qwerty 18 asdf 18
20​	blah 19	qwerty 19	asdf 19		blah 19 qwerty 19 asdf 19
21​	blah 20	qwerty 20	asdf 20		blah 20 qwerty 20 asdf 20

Any advice as to how I can get a count on those categories, without having to manually fix everything in E line by line (got a few thousand lines of the input and around 300 categories in the ABC)?

Thanks very much in advance for the help!!

 

[Marc L]

Hi,​

​

in the Data menu use the Text To Columns feature …​