CODEWITHSUNDEEP
excelexcel-formulavba
KARTIKE JAIN
KARTIKE JAIN

Reputation: 47

Multiple Words in one Cell, How to separate without delimiting

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.

Upvotes: 1

Views: 108

Answers (3)

Julian Kuchlbauer
Julian Kuchlbauer

Reputation: 895

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.

Upvotes: 1

Mats Lind
Mats Lind

Reputation: 934

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

Upvotes: 1

Ulli Schmid
Ulli Schmid

Reputation: 1167

  • 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

Upvotes: 1

Related Questions