Split field
 

We would like to split a field which has sometimes 3,4,5, word in the field
the split should be as follows left word shall be copied in 1 cell, right
word in 1 cell ,all middle words together in 1 cell

Split field
 

On Fri, 28 Dec 2007 10:13:02 -0800, dk wrote:

We would like to split a field which has sometimes 3,4,5, word in the field
the split should be as follows left word shall be copied in 1 cell, right
word in 1 cell ,all middle words together in 1 cell


A1: original string
B1: =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)

C1:
=MID(TRIM(A1),LEN(B1)+2,
LEN(TRIM(A1))-(LEN(B1)+LEN(D1))-1)

D1:
=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ","")))),255)
--ron

Split field
 

thanks ok

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?


"dk" wrote:

We would like to split a field which has sometimes 3,4,5, word in the field
the split should be as follows left word shall be copied in 1 cell, right
word in 1 cell ,all middle words together in 1 cell

Split field
 

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

Split field
 

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

Split field
 

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

Split field
 

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


And here is another version, that uses just native VBA and avoids "Regular
Expressions"

================================================== =
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 aStr As Variant
Dim sTemp As String
Dim i As Long

For Each c In Selection
c.Offset(0, 1).Resize(1, 3).ClearContents
aStr = Split(Application.WorksheetFunction.Trim(c.Value), " ")
Select Case UBound(aStr)
Case Is = 0
c.Offset(0, 2).Value = aStr(0)
Case Is = 1
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 2).Value = aStr(1)
Case Is = 2
c.Offset(0, 1).Value = aStr(0) & " " & aStr(1)
c.Offset(0, 2).Value = aStr(2)
Case Is = 3
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 3).Value = aStr(UBound(aStr))
For i = LBound(aStr) + 1 To UBound(aStr) - 1
sTemp = sTemp & aStr(i) & " "
Next i
c.Offset(0, 2).Value = Trim(sTemp)
End Select
Next c
End Sub
=====================================
--ron

Split field
 

And here is another version, that uses just native VBA and avoids "Regular
Expressions"

Anticipating a reply by me eh? <g

aStr = Split(Application.WorksheetFunction.Trim(c.Value), " ")
........
Case Is = 3
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 3).Value = aStr(UBound(aStr))
For i = LBound(aStr) + 1 To UBound(aStr) - 1
sTemp = sTemp & aStr(i) & " "
Next i
c.Offset(0, 2).Value = Trim(sTemp)

1. Just as a point of information, the LBound an array created by the Split
function is always zero.

2. You can simplify, at least I think it would be considered a
simplification, the above code like this...

Case Is =3
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 3).Value = aStr(UBound(aStr))
aStr(0) = ""
aStr(UBound(aStr)) = ""
c.Offset(0, 2).Value = Trim(Join(aStr, " "))

Again, as a point of information, the default delimiter for the Split and
Join functions is a space character, so the 2nd argument in both of those
function calls could be omitted.

Rick

Split field
 

1. Just as a point of information, the LBound an array created by the
Split function is always zero.

The above was supposed to have said, the LBound for an array created by the
Split function is always zero **no matter what the Option Base setting is**.

Rick

Split field
 

On Sun, 30 Dec 2007 22:47:17 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Again, as a point of information, the default delimiter for the Split and
Join functions is a space character, so the 2nd argument in both of those
function calls could be omitted.

Yes, that's true. But that gets us back into the discussion we've had before
about omitting defaults, and how, under certain circumstances, it can be
confusing.


--ron

Split field
 

On Mon, 31 Dec 2007 05:32:27 -0500, Ron Rosenfeld
wrote:

On Sun, 30 Dec 2007 22:47:17 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Again, as a point of information, the default delimiter for the Split and
Join functions is a space character, so the 2nd argument in both of those
function calls could be omitted.

Yes, that's true. But that gets us back into the discussion we've had before
about omitting defaults, and how, under certain circumstances, it can be
confusing.


--ron

I meant to add, "especially for the LBound of the Split function, as that's
where I've been burned".
--ron

Split field
 

can you please explain exactly how what &when to use this macro we are a new
user please explan step by step wehave wasted hours already
Thank You

"Rick Rothstein (MVP - VB)" wrote:

And here is another version, that uses just native VBA and avoids "Regular
Expressions"

Anticipating a reply by me eh? <g

aStr = Split(Application.WorksheetFunction.Trim(c.Value), " ")
........
Case Is = 3
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 3).Value = aStr(UBound(aStr))
For i = LBound(aStr) + 1 To UBound(aStr) - 1
sTemp = sTemp & aStr(i) & " "
Next i
c.Offset(0, 2).Value = Trim(sTemp)

1. Just as a point of information, the LBound an array created by the Split
function is always zero.

2. You can simplify, at least I think it would be considered a
simplification, the above code like this...

Case Is =3
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 3).Value = aStr(UBound(aStr))
aStr(0) = ""
aStr(UBound(aStr)) = ""
c.Offset(0, 2).Value = Trim(Join(aStr, " "))

Again, as a point of information, the default delimiter for the Split and
Join functions is a space character, so the 2nd argument in both of those
function calls could be omitted.

Rick

Split field
 

On Wed, 9 Jan 2008 12:50:01 -0800, dk wrote:

can you please explain exactly how what &when to use this macro we are a new
user please explan step by step wehave wasted hours already
Thank You


Here's what I wrote before, although I used the term 'UDF' where I should have
used 'Macro'.

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

I believe the above answers your "how" question. And I really don't know how
to make it more clear.

You will have to describe what happened as you go through each of the above
steps in order for me to help further.

As far as your "when" question, I thought that would be obvious -- you use it
when you want to split data as you requested.

The "what" question is that which you asked.
--ron

Split field
 

the macro or vb don't understand because the first name doesn't work out
with the trim function so we want to use the macro

"Ron Rosenfeld" wrote:

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


And here is another version, that uses just native VBA and avoids "Regular
Expressions"

================================================== =
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 aStr As Variant
Dim sTemp As String
Dim i As Long

For Each c In Selection
c.Offset(0, 1).Resize(1, 3).ClearContents
aStr = Split(Application.WorksheetFunction.Trim(c.Value), " ")
Select Case UBound(aStr)
Case Is = 0
c.Offset(0, 2).Value = aStr(0)
Case Is = 1
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 2).Value = aStr(1)
Case Is = 2
c.Offset(0, 1).Value = aStr(0) & " " & aStr(1)
c.Offset(0, 2).Value = aStr(2)
Case Is = 3
c.Offset(0, 1).Value = aStr(0)
c.Offset(0, 3).Value = aStr(UBound(aStr))
For i = LBound(aStr) + 1 To UBound(aStr) - 1
sTemp = sTemp & aStr(i) & " "
Next i
c.Offset(0, 2).Value = Trim(sTemp)
End Select
Next c
End Sub
=====================================
--ron

Split field
 

On Wed, 9 Jan 2008 16:28:01 -0800, dk wrote:

the macro or vb don't understand because the first name doesn't work out
with the trim function so we want to use the macro

What happened when you followed the instructions in my post???

I have repeated it below. Go through this paragraph by paragraph (starting with
paragraph 4 where I wrote <alt-F11) and tell me exactly what you did and what
the result was.

=============================================
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 Macro could possibly be simplified, but I believe this solution will work.

To enter the Macro, <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 Macro, 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