Separating Text and putting the results elsewhere

[Immobius]

Good day!

I have a scenario that I want to count the number of times specific words come up so I can trend some issues. I pull data from a text file into Excel and want to run through some lengthy text in about 300 cells for keywords. I figure counting the all the words will give me an idea of what's the most popular.

How do I take all the words in several cells and break them out into rows? I would prefer a new sheet, but it doesn't have to be. Here's an example of what I have and what I would like to do:

C1: CSAR - Implementation problem
C2: HD error - BSOD
C3: Application froze while doing dispatch

I would like to extract that data and put each word into its own row:

A1: CSAR
A2: Implementation
A3: problem
A4: HD
A5: error

etc.

I have tried putting Char(10) in place of all the spaces (of the original data) so that I can use a delimiter to try filtering, Text to Columns, and whatever else I could think of but that didn't break out the words into rows, just columns.

Does anyone have some ideas on how to get the words broken out of the cell and put it into rows? The text to columns idea just isn't working for me.

Thanks in advance. I've been pouring over the 'net for some ideas and not getting anywhere

- Christopher

[olasa]

This should give you a hint:
http://www.excelforum.com/showthread.php?t=347882

I also included two examples:
a) =COUNTIF(C1:C300,"*CSAR*")
b) =SUMPRODUCT(COUNTIF(C1:C300,{"*CSAR*","*Implementation*"}))

In your case:
C1: CSAR - Implementation problem
C2: HD error - BSOD
C3: Application froze while doing dispatch
the a) example will result in =1
the b) example will result in =2

Ola Sandstrom


Note:
The formulas will Only count a word once/cell.
In this case together with the a) example
C1: CSAR - Implementation problem CSAR
...CSAR will be counted as 1 not 2.

A1: *CSAR*
=COUNTIF(C1:C300,A1)

This is not allowed
=SUMPRODUCT(COUNTIF(C1:C300,{A1,"*Implementation*"}))

A1: CSAR
="*"&B1&"*"
=*CSAR*

...

[Immobius]

Thank you for your response!! The counting is pretty much exactly what I need, the only problem is that there are hundreds of rows to sort through and let's say I find "HD" in one sentance, I may find it in 15 other sentances too. So I'm trying to pull out how many times "HD" comes up entirely. So this is a super start, just looking for a way to get that data into a column where I can count the words themselves...

Here's what I have so far (and did about a year ago, but now can't remember)

Out of the 300 cells of text, I took each word in each cell and inserted a Char(10) where there was a space using this formula:
=SUBSTITUTE(TRIM(B355)," ",CHAR(10))

THEN I took the resulting value and copied it somehow to a new sheet. These words came out, one per row, and made something like, 20,000 rows of words. Then I calculated how many times each came up, sorted them alphabetically and it came out something like this:

a 32
able 6
accesed 1
access 6
accessible 1
account 31
accounts 3
acct 1
acct, 1
acct/existing 1


Just how do you copy all the words between the return carraiges to a new column? I hate not leaving notes for myself...

- Christopher

[olasa]

If I understand correct. You have
C1: CSAR - Implementation problem
C2: HD error - BSOD
C3: Application froze while doing dispatch
and want to separate each word into columns? To get One word/ cell

Easy.
Mark C1 to C3
Goto the Menu. Select Data and Text to Columns...
Select Delimited... Next> ... (I think you will manage from here)

Ola Sandstrom


This might also be of interest:
http://www.cof.orst.edu/net/software/excel/tips/
Othervise Transpose can also be accomplished with a formula. =OFFSET($A$1,COL()-1,0)
The idea is that the formula offsets down as the formula is copied to the right col().

[Claire Duncan]

I have a similar problem.

I have a string of 288 letters and numbers, no spaces . I want to separate them in order so that I have 72 cells with 4 characters in each cell.

Example: a111b111c111d111
would return: a111 b111 c111 d111

I used to know Excel-- but it's been a long time.

Thanks

[olasa]

Then I would do this:

A1: a111b111c111d111
B1: =MID($A$1,1+(COLUMN()-2)*4,4)
Copy to the right


Ola Sandstrom