Solved

# split text to other columns in row

Posted on 2013-05-19
310 Views
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
``````
0
Question by:PeterBaileyUk
• 7
• 5
• 4

LVL 43

Accepted Solution

Saqib Husain, Syed earned 250 total points
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

[ fanpages ] earned 250 total points
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
``````

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].

BFN,

fp.
0

Author Comment

how do i send the string?
0

LVL 35

Expert Comment

^ * copy these cells down :)
0

Author Comment

I called the sub:
call splitwords

should cel be a keyword cell?
0

LVL 35

Expert Comment

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
``````
0

Author Comment

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

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

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

^ ssaqibh: You continue.  I'll hold off, unless I'm needed further.
0

Author Comment

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

I have split the points as both efforts commendable.

thank you
0

LVL 43

Expert Comment

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

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

fp,

"Private Messaging" facility
0

LVL 35

Expert Comment

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

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …