On Sun, 30 Dec 2007 07:36:00 -0800, dk wrote:

only the last if there is 1 it's should go in column 2 all others in column
1 bassically only the last in column 2, column 2 shall not be empty

"Ron Rosenfeld" wrote:

On Sat, 29 Dec 2007 19:39:00 -0800, dk wrote:

how abou a fieild with only 2,3 words havin the first column everything
besides last word in 1 column last word in second column?




If there are only 2,3 words, do you want the last word in column 3 (lined up
with the last word if there are more than 3 words) or do you want the last word
in column 2?

What about if there is only one word?

What do you want to display if there are no words? -- <blank? <error
message?
--ron


If I understand you correctly (and you can check by looking at the comments at
the top of this VBA macro), then this should do what you want.

It could be done with formulas, but it would be exceedingly complex and time
consuming to devise.

The UDF could possibly be simplified, but I believe this solution will work.

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, select your range of cells. <alt-F8 opens the Macro dialog
box. Select the Macro, and <run.

Let me know if this does what you need.

================================================== =
Option Explicit
Sub SplitSpecial()
'splits multi-word string into adjacent
' columns as follows
'1 word -- col2
'2 words -- col1 & col2
'3 words -- 1st 2 in col1; last in col2
'4+ words -- 1st in col1; last in col3; rest in col2
Dim c As Range
Dim re As Object, mc As Object, m As Object
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(\w+\b)?(\s*(.*?)\s*)(\b\w+$)"

For Each c In Selection
c.Offset(0, 1).Resize(1, 3).ClearContents
If re.test(c.Text) Then
Set mc = re.Execute(c.Text)
If mc(0).submatches.Count 0 Then
If InStr(1, mc(0).submatches(2), " ") = 0 Then
c.Offset(0, 1).Value = Trim(mc(0).submatches(0) & _
" " & mc(0).submatches(2))
c.Offset(0, 2).Value = mc(0).submatches(3)
Else
c.Offset(0, 1).Value = mc(0).submatches(0)
c.Offset(0, 2).Value = mc(0).submatches(2)
c.Offset(0, 3).Value = mc(0).submatches(3)
End If
End If
End If
Next c
End Sub
=============================================
--ron