1

Due to an erroneous online survey setup, the answers of a multiple choice, select all that apply question have all come together in one cell. For example:

All the selectable options are as follows:

A12
B1234
C3
D845
E00091
F

Cells with responses look as follows:

Cell A1: A12C3E00091
Cell A2: B1234F
Cell A3: C3D845F

And there are 100 cells like these with random responses.

I need to somehow automate the data extraction and then count each option.

I tried using Left, Right etc. Its not really helping.

I did use Find and then tried to extract data, but I'm unsure of a function that works from a specific start point within a cell.

If anyone could please help with this.

Thanks.

Community
  • 1
  • 1
KARTIKE JAIN
  • 47
  • 4
  • I think the answer given [here](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) would help – Michael Russo Aug 02 '16 at 11:49
  • Have you tried using the `instr` function? There are only 6 possibilities you need to check so you could just set up 6 if statements checking each cell and handle each result accordingly – nick Aug 02 '16 at 11:51

3 Answers3

1
  • Copy your data to range A2:A101
  • into B1:G1, enter A,B,C,D,E,F
  • into B2, enter =LEN($A2)-LEN(SUBSTITUTE($A2,B$1,""))
  • copy B2 to B2:G101

result: enter image description here

Ulli Schmid
  • 1,167
  • 1
  • 8
  • 16
1

Copy this into a vba module and use Countwords(RangeString, SearchTerm) as a Cell function:

Public Function CountWords(r As String, Search As String) As Integer
Dim a As Variant, str As String, Count As Integer
For Each a In Range(r).Value
    str = str & a
Next a
Count = (Len(str) - Len(Replace(str, Search, ""))) / Len(Search)
CountWords = Count
End Function

So =CountWords("A1:A10";"A12") Counts the A12s in the Range A1:A10.

Julian Kuchlbauer
  • 895
  • 1
  • 8
  • 17
1

Kartike,

If I understand you right, you want to know which options are represtented in a cell with responses. I would say that you are right to use find. To test if option 1 is in cell A1 run:

=ISNUMBER(FIND("A12"; A1))

which returns TRUE if the string "A12" is included, and FALSE otheriwse. With the answer strings in a column down from A2 and the options strings in a row right from B1 you could get the full table of options by filling the rows and columns with

=ISNUMBER(FIND(B$1;$A2))

starting from cell B2.

Regards, Mats

Mats Lind
  • 914
  • 7
  • 19