?
Solved

split text to other columns in row

Posted on 2013-05-19
16
Medium Priority
?
356 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 1000 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 1000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

719 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