splitting names from one cell to two cells

[Jock]

In cells in column E, I have names (could be John Doe, Dr John Stephen Henry
Doe, could be a company name) which are seperated by the character "v" which
indicates parties on one side versus parties on the other side.
I only need to split the data in cells in E which will be dictated by
ActiveCell.Row
How can I split the data in a cell in E into everything to the left of the
"v" to sheet2, lastrow + 1 (I've already got that bit working) col C, and
everything after the "v" to col E on that same sheet?

The line below currently copies all the data from a cell in wbS, col E to
the other col C on the other sheet. I need to split the data to C and E on
the new sheet (col D will have the v in it).
..Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo)

I hope that makes sense!

 

[Mike H]

jock,

I think the issue with doing this, i.e. splitting a cells contents either
side of a "v", is what happens if there's another v in the cell (Dr, Vernon
somename) for example so this may be more difficult than it seems.

However, here's 2 lines of code that split the active cell at the 'v' to the
cells either side


ActiveCell.Offset(, -1).Value = Left(ActiveCell.Value, InStr(1,
ActiveCell.Value, "v", vbTextCompare) - 1)


ActiveCell.Offset(, 1).Value = Mid(ActiveCell.Value, InStr(1,
ActiveCell.Value, "v", vbTextCompare) + 1)

If you post more of your code and some sample data then perhaps it can be
made more specific.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Try

Dim strData As String

strData = ActiveCell.Text
strData = "firstdata v seconddata"
If InStr(1, strData, " v ", vbBinaryCompare) Then
MsgBox Trim(Left(strData, InStr(1, strData, " v ", vbBinaryCompare)))
MsgBox Trim(Mid(strData, InStr(1, strData, " v ", vbBinaryCompare) + 3))
End If

 

[helene and gabor]

Hello Jock,

A simple way to achieve name separation in Excel 2007 consists of the
following steps:
Leave column F free
mark your list of names to be separated
data
text to column
delimited
Convert text to Column Wizard delimiter: other, v (unmark others)
Column E will have all data up to "v", Column F the rest.

best regards

Gabor Sebo

 

[Jock]

How simple is that? It falls down though when there's a 'v' in a name in a
cell but I can use that function for other projects.
Thanks.