Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 7
  • 5
  • 4
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
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 ]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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
[ 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now