Hi,

I have a text string I need to split into seperate cells for each
word.

For example, if this was the value of A4;

The fat cat jumped over the mat

I would want;

B5 The
C5 fat
D5 cat
E5 jumped

etc etc

Each of the words in the string may be different lengths but they will
always be seperated by a space.

Look at DataText to columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"keri" wrote in message oups.com...
| Hi,
|
| I have a text string I need to split into seperate cells for each
| word.
|
| For example, if this was the value of A4;
|
| The fat cat jumped over the mat
|
| I would want;
|
| B5 The
| C5 fat
| D5 cat
| E5 jumped
|
| etc etc
|
| Each of the words in the string may be different lengths but they will
| always be seperated by a space.
|

On Apr 12, 11:14 am, "Niek Otten" wrote:
Look at DataText to columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"keri" wrote in ooglegroups.com...

| Hi,
|
| I have a text string I need to split into seperate cells for each
| word.
|
| For example, if this was the value of A4;
|
| The fat cat jumped over the mat
|
| I would want;
|
| B5 The
| C5 fat
| D5 cat
| E5 jumped
|
| etc etc
|
| Each of the words in the string may be different lengths but they will
| always be seperated by a space.
|

Or set up a looping istr search

On 12 Apr 2007 12:35:49 -0700, "Craig B" wrote:

Or set up a looping istr search

OR just use the Split function
--ron

On 13 Apr, 00:41, Ron Rosenfeld wrote:
On 12 Apr 2007 12:35:49 -0700, "Craig B" wrote:

Or set up a looping istr search

OR just use the Split function
--ron

Hi,

Thanks for your replies. I tried using text to columns but it appears
I have to specify the length of the array, so this does not work.

I had not heard of the split function but have found this code;

Sub SplitDemo()
Dim txt As String
Dim x As Variant
Dim i As Long
txt = ActiveSheet.Range("a23")
x = Split(txt, " ")
For i = 0 To UBound(x)
Debug.Print x(i)
Next i

What I am unsure how to do is split the text into columns B23 - L23 as
I am unsure how to reference each part of the split text to name a
destination.

Many thanks.

Hi Keri, try this

Sub single_Split()
'
Dim x As String, s As Variant

x = Range("A4")
s = Split(x, " ")
Range("B5").Value = s(0)
Range("C5").Value = s(1)
Range("D5").Value = s(2)
Range("E5").Value = s(3)
End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

On 13 Apr 2007 01:30:00 -0700, "keri" wrote:

On 13 Apr, 00:41, Ron Rosenfeld wrote:
On 12 Apr 2007 12:35:49 -0700, "Craig B" wrote:

Or set up a looping istr search

OR just use the Split function
--ron

Hi,

Thanks for your replies. I tried using text to columns but it appears
I have to specify the length of the array, so this does not work.

I had not heard of the split function but have found this code;

Sub SplitDemo()
Dim txt As String
Dim x As Variant
Dim i As Long
txt = ActiveSheet.Range("a23")
x = Split(txt, " ")
For i = 0 To UBound(x)
Debug.Print x(i)
Next i

What I am unsure how to do is split the text into columns B23 - L23 as
I am unsure how to reference each part of the split text to name a
destination.

Many thanks.

Text to columns has an option which allows you to specify a delimiter which, in
your case, you would specify a <space. Of course, since your source is in A4
and your destination is B5, you might also have to do some copy/paste
operations.

To use the Split function, given your parameters in the OP, try this:

-------------------------------------------------

Option Explicit

Sub ParseString()
Dim rSrc As Range
Dim rDest As Range
Const sDelim As String = " "
Dim Temp As Variant
Dim i As Long

Set rSrc = Range("A4")
Set rDest = Range("B5")

Temp = Split(rSrc.Text, sDelim, -1)

For i = 0 To UBound(Temp)
rDest.Offset(0, i).Value = Temp(i)
Next i

End Sub
==================================================



--ron

On Fri, 13 Apr 2007 03:19:51 -0700, Les Stout wrote:

Hi Keri, try this

Sub single_Split()
'
Dim x As String, s As Variant

x = Range("A4")
s = Split(x, " ")
Range("B5").Value = s(0)
Range("C5").Value = s(1)
Range("D5").Value = s(2)
Range("E5").Value = s(3)
End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Just as an additional note (I hit send too quickly), the Split function, as a
default, will use <space as the delimiter and also return all of the strings,
so the following routine is equivalent and a bit shorter:

===============================================
Option Explicit

Sub ParseString()
Dim rSrc As Range
Dim rDest As Range
Dim Temp As Variant
Dim i As Long

Set rSrc = Range("A4")
Set rDest = Range("B5")

Temp = Split(rSrc.Text)

For i = 0 To UBound(Temp)
rDest.Offset(0, i).Value = Temp(i)
Next i

End Sub
================================================
--ron

On Fri, 13 Apr 2007 08:03:40 -0400, Ron Rosenfeld
wrote:

On Fri, 13 Apr 2007 03:19:51 -0700, Les Stout wrote:

Hi Keri, try this

Sub single_Split()
'
Dim x As String, s As Variant

x = Range("A4")
s = Split(x, " ")
Range("B5").Value = s(0)
Range("C5").Value = s(1)
Range("D5").Value = s(2)
Range("E5").Value = s(3)
End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Just as an additional note (I hit send too quickly), the Split function, as a
default, will use <space as the delimiter and also return all of the strings,
so the following routine is equivalent and a bit shorter:

===============================================
Option Explicit

Sub ParseString()
Dim rSrc As Range
Dim rDest As Range
Dim Temp As Variant
Dim i As Long

Set rSrc = Range("A4")
Set rDest = Range("B5")

Temp = Split(rSrc.Text)

For i = 0 To UBound(Temp)
rDest.Offset(0, i).Value = Temp(i)
Next i

End Sub
=============================================== =
--ron


This is out of order and should be a followup response to keri's post.
--ron