Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
sparx
 
Posts: n/a
Default Split sentences into words


Does anybody know of a formula that can look at a sentence of say 3 or 4
words and then look for the spaces inbetween the words and put each word
in a different cell underneath each other?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=515292

  #2   Report Post  
Posted to microsoft.public.excel.misc
Andrew Taylor
 
Posts: n/a
Default Split sentences into words

You can just about do it with worksheet functions, but it's rather
long-winded (and dependent on correct input)

With the sentence in A1
A2: =LEFT(A1,FIND(" ",A1)-1)
A3: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)
A4: "and so on"...

It's a bit easier if you have some helper cells that just contain the
positions of the spaces:

A2: =FIND(" ",$A$1)
A3: =FIND(" ",$A$1&" ",A2+1) ' append space to avoid special case for
last word
and copy the A3 down a few rows
B2: =LEFT($A$1,A2-1)
B3 =MID($A$1,A2+1,A3-A2-1)
and copy B3 down.

HTH
Andrew


sparx wrote:
Does anybody know of a formula that can look at a sentence of say 3 or 4
words and then look for the spaces inbetween the words and put each word
in a different cell underneath each other?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=515292


  #3   Report Post  
Posted to microsoft.public.excel.misc
Kevin B
 
Posts: n/a
Default Split sentences into words

VBA has a function named SPLIT which does what you want. The syntax for the
function is SPLIT(String value, Delimiter)

If you place a sentence in column A row 1 of Sheet 1, and place another in
column a row 2, you can run the following code to parse the words separated
by spaces and place them on sheet 2 column A in a column. Hope it helps to
point you in the general direction.

Sub SplitSentences()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim l As Long
Dim strSentence As String
Dim varArray As Variant
Dim varItems As Variant

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

ws1.Activate
Range("A1").Select
strSentence = ActiveCell.Value

Do Until strSentence = ""
ws2.Activate
Range("A1").Select
varArray = Split(strSentence, " ")
varItems = varArray
For Each varItems In varArray
ActiveCell.Offset(l).Value = varItems
l = l + 1
Next varItems
ws1.Activate
ActiveCell.Offset(1).Select
strSentence = ActiveCell.Value
Loop

Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing

--
Kevin Backmann


"sparx" wrote:


Does anybody know of a formula that can look at a sentence of say 3 or 4
words and then look for the spaces inbetween the words and put each word
in a different cell underneath each other?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=515292


  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default Split sentences into words

On Wed, 22 Feb 2006 07:38:30 -0600, sparx
wrote:


Does anybody know of a formula that can look at a sentence of say 3 or 4
words and then look for the spaces inbetween the words and put each word
in a different cell underneath each other?


1. Use the Data/Text to Columns wizard with <space as the delimiter.

or

2. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then, with your sentence in A1:

B1: =REGEX.MID($A1,"\w+",COLUMNS($A:A))

and copy/drag across as far as needed.

If it is an issue, there is an option to distribute the morefunc.xll add-in
with the workbook.


--ron
  #5   Report Post  
Posted to microsoft.public.excel.misc
Sloth
 
Posts: n/a
Default Split sentences into words

You will first need to transpose the data to be in one column only.

Select the column you want to split
In the "Data" menu, select "Text to Columns"
Select "Delimited" and then click "Next"
Check only the box next to Space, and maybe the one next to "Treat
consecutive delimiters as one" and then click "Finish"

This will split all cells in the column you selected and push the words to
the right in other cells as you described you need. You can then tranpose
the data to get the orientation you want.



"sparx" wrote:


Does anybody know of a formula that can look at a sentence of say 3 or 4
words and then look for the spaces inbetween the words and put each word
in a different cell underneath each other?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=515292




  #6   Report Post  
Posted to microsoft.public.excel.misc
sparx
 
Posts: n/a
Default Split sentences into words


Thanks for the response - I will try each and will try it in my
worksheet.

Thanks again.


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=515292

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text to Columns - Only want to split SPECIFIC NUMBER of times NewKid Excel Worksheet Functions 9 February 3rd 06 03:40 PM
Split the words Kelvin Lee Excel Discussion (Misc queries) 1 October 10th 05 03:22 PM
How do I split a cell of 2 words into two cells Steven S Excel Worksheet Functions 4 September 13th 05 03:57 PM
triadic combinations of words jayock02 Excel Worksheet Functions 1 June 19th 05 02:10 AM
Spell Checking - Special Words Not Picked Up by Excel Hans Emilio Excel Discussion (Misc queries) 4 May 25th 05 02:25 PM


All times are GMT +1. The time now is 11:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"