Link to home
Create AccountLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

how do i send the string?
^ * copy these cells down :)
I called the sub:
call splitwords


should cel be a keyword cell?
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

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
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
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?
^ ssaqibh: You continue.  I'll hold off, unless I'm needed further.
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
I have split the points as both efforts commendable.

thank you
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?
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).
fp,

"Private Messaging" facility
I have not heard about this. Where is it?
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).