Split string into seperate cells

[keri]

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.

 

[Niek Otten]

Look at Data>Text to columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.
|

 

[Craig B]

Look at Data>Text to columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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

 

[Ron Rosenfeld]

Or set up a looping istr search

OR just use the Split function
--ron

 

[keri]

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.

 

[Les Stout]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

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

 

[Ron Rosenfeld]

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