excel split separator

[ram567]

I have column c have text which contain ,
for example xxx24,yyyyyyyyy,abcde,mnopqrst,lmn12,abc13
i need to sepearate
column c xxxx24, abcde,lmn12,abc13
column m yyyyyyyyy,mnopqrst

before "," if it is <= 5 words
put it in column c
else column m


is it possible
could you provide the code

 

[SkipVought]

Hi,

Check out Data > Text to Columns - DELIMITED and select the COMMA delimiter.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[xlbo]

you will need VBA for this I would've thought (without lots of manual workarounds)

The SPLIT function should get you most of the way there

With some sample data in C1, this code provides the split you need:

Sub test()
Dim strTest As String
Dim arrTest As Variant
Dim strColC As String, strColM As String

strTest = [C1]
arrTest = Split(strTest, ",")

For i = LBound(arrTest) To UBound(arrTest)
    If Len(arrTest(i)) <= 5 Then
        strColC = strColC & "," & arrTest(i)
    Else
        strColM = strColM & "," & arrTest(i)
    End If
Next i

strColC = Right(strColC, Len(strColC) - 1)
strColM = Right(strColM, Len(strColM) - 1)

MsgBox strColC & vbCrLf & strColM

End Sub

You would need to loop down your column a cell at a time and then commit strColC & strColM to the new cells (C & M)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ram567]

xlbo
thank yu very much the above code do i have to put it in module

 

[xlbo]

yes you do

This will only work for 1 cell as an example - you will need to set up a loop for it to run through all cells

If you have any further questions on this, please post in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question