split text to other columns in row

I have a little code that spits the text into an array but not sure how now to get each word into its own column on its relevant row.

ex

       A1          A2                                a3               a4      a5
1     xx1    "hello world"                "hello"        world
2     xx2    "goodbye world now"  "goodbye     world     now


a2 is variable in number of words

Function ConvertTextRange(TextIn As String)


Dim sText As String, arText
sText = TextIn
arText = Split(sText, " ")




End Function

Open in new window

PeterBaileyUkAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Although it is possible to do this using a function, it would be neater to use a sub in its place because the function needs to be entered in each of the cells and you must either know how many cells each phrase requires or fill the function to the maximum expected columns and pad the superfluous with blanks. On the other hand a sub would post values only where required.

Sub splitwords()
    For Each cel In Range("A1:A" & Range("A1").End(xlDown).Row)
        artext = Split(cel, " ")
        cel.Offset(, 2).Resize(, UBound(artext)) = artext
    Next cel
End Sub
0
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

If you changed your function to something like this:

Public Function ConvertTextRange(ByVal strText As String, _
                                 ByVal lngIndex As Long) As String
                              
  On Error Resume Next
  
  ConvertTextRange = Split(strText & " ", " ")(lngIndex)
                              
End Function

Open in new window


In cell [A3] you could use this formula:
=ConvertTextRange(B1,0)

Cell [A4]:
=ConvertTextRange(B1,1)

Cell [A5]:
=ConvertTextRange(B1,2)

Then copy these cell down the respective columns as required.

However, you could add Visual Basic for Applications code within the Worksheet_Change() event to automatically populate the cells [A3:A5] (& beyond, if required) when any text is entered into cell [A2], without the need for individual in-cell formula in any cell on row [A].

Please advise if this is a preferred option.

BFN,

fp.
0
 
PeterBaileyUkAuthor Commented:
how do i send the string?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
[ fanpages ]IT Services ConsultantCommented:
^ * copy these cells down :)
0
 
PeterBaileyUkAuthor Commented:
I called the sub:
call splitwords


should cel be a keyword cell?
0
 
[ fanpages ]IT Services ConsultantCommented:
should cel be a keyword cell?

If you have 'Option Explicit' set at the top of the code module, please change ssaqibh's code to read:

Sub splitwords()

  Dim artext As Variant ' *** Insert this line
  Dim cel As Range ' *** Insert this line

    For Each cel In Range("A1:A" & Range("A1").End(xlDown).Row)
        artext = Split(cel, " ")
        cel.Offset(, 2).Resize(, UBound(artext)) = artext
    Next cel

End Sub

Open in new window

0
 
PeterBaileyUkAuthor Commented:
it appears i have two methods? ok I got  39178328 and agree when the cell a2 is populated it should do as you said.

I see this too:

Sub splitwords()
    For Each cel In Range("A1:A" & Range("A1").End(xlDown).Row)
        artext = Split(cel, " ")
        cel.Offset(, 2).Resize(, UBound(artext)) = artext
    Next cel
End Sub

for reference the cell to be split is in f2 downwards and cells are empty from column N onwards hope that makes sense
0
 
Saqib Husain, SyedEngineerCommented:
Change it to

Sub splitwords()
    For Each cel In Range("F2:F" & Range("F2").End(xlDown).Row)
        artext = Split(cel, " ")
        cel.Offset(, 8).Resize(, UBound(artext)) = artext
    Next cel
End Sub
0
 
[ fanpages ]IT Services ConsultantCommented:
So, you only wish to split cells in column [F], from row 2 onwards.

Sorry, I was unclear on the relevance of column [N].

Is that where the individual "words" of the text in column [F] should be placed?

First "word" in column [N], second in [O], third in [P], & so on?
0
 
[ fanpages ]IT Services ConsultantCommented:
^ ssaqibh: You continue.  I'll hold off, unless I'm needed further.
0
 
PeterBaileyUkAuthor Commented:
ok I got it I went with the sub as I am only experimenting at present but changed to this as it was clipping off the last word due to array starting at 0:

Sub splitwords()
Dim cel As Range
Dim artext
    For Each cel In Range("f2:f" & Range("f2").End(xlDown).Row)
        artext = Split(cel, " ")
        cel.Offset(, 7).Resize(, UBound(artext) + 1) = artext
    Next cel
End Sub
0
 
PeterBaileyUkAuthor Commented:
I have split the points as both efforts commendable.

thank you
0
 
Saqib Husain, SyedEngineerCommented:
fp, You are most welcome to carry on. Every extra bit of knowledge is useful. BTW is it possible to get connected with you elsewhere like on FB?
0
 
[ fanpages ]IT Services ConsultantCommented:
Thanks for closing the question so promptly, Peter.

Good luck with the rest of your project.

ssaqibh: I will add a contact point to my profile for you.

PS. Experts-Exchange.com do with a "Private Messaging" facility for Experts to exchange messages with pre-defined contacts (not for circumventing the points system).
0
 
Saqib Husain, SyedEngineerCommented:
fp,

"Private Messaging" facility
I have not heard about this. Where is it?
0
 
[ fanpages ]IT Services ConsultantCommented:
Ooops! :)

I missed an important word out of my "PS.".

I'll try that again...

PS. Experts-Exchange.com could do with a "Private Messaging" facility for Experts to exchange messages with pre-defined contacts (not for circumventing the points system).
0
All Courses

From novice to tech pro — start learning today.