Does anybody know of a formula that can look at a sentence of say 3 or 4 words and then look for the spaces in between the words and put each word in a different cell underneath each other.
Does anybody know of a formula that can look at a sentence of say 3 or 4 words and then look for the spaces in between the words and put each word in a different cell underneath each other.
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.
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.
Code: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
There are two methods that you can try out. First use the Data/Text to Columns wizard with <space> as the delimiter. And the second option is by downloading and installing a morefunc.xll add-in program which is freely available on internet. 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.
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.
Bookmarks