Solved

split text to other columns in row

Posted on 2013-05-19
16
349 Views
Last Modified: 2013-05-19
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
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
16 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 39178326
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
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 250 total points
ID: 39178328
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
 

Author Comment

by:PeterBaileyUk
ID: 39178329
how do i send the string?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39178330
^ * copy these cells down :)
0
 

Author Comment

by:PeterBaileyUk
ID: 39178331
I called the sub:
call splitwords


should cel be a keyword cell?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39178337
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
 

Author Comment

by:PeterBaileyUk
ID: 39178339
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39178340
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39178341
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39178342
^ ssaqibh: You continue.  I'll hold off, unless I'm needed further.
0
 

Author Comment

by:PeterBaileyUk
ID: 39178344
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 39178346
I have split the points as both efforts commendable.

thank you
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39178347
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39178349
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39178367
fp,

"Private Messaging" facility
I have not heard about this. Where is it?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39178413
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question