Hello
Windows XP Professional SP2
Office professional 2003 SP1

I have a column of data that has the following contents

contact 1 | contact2 | contact 3

I'd like to select the first contact. Each contact name is different
in size and usually there is a first and last name for each contact.
How do I extract to the first contact from this record?

Thank you
Bob

Bob,

Is the data really delimited by a | character? If so, use the
following code:


Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
Debug.Print F



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
...
Hello
Windows XP Professional SP2
Office professional 2003 SP1

I have a column of data that has the following contents

contact 1 | contact2 | contact 3

I'd like to select the first contact. Each contact name is
different
in size and usually there is a first and last name for each
contact.
How do I extract to the first contact from this record?

Thank you
Bob

Not familar with this type of code. Is it VB code?
If so, how do I use it?

TIA
Bob

On Thu, 24 Mar 2005 14:45:14 -0600, "Chip Pearson"
wrote:

Bob,

Is the data really delimited by a | character? If so, use the
following code:


Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
Debug.Print F

Bob,

Yes, it is VBA code. Press ALT+F11 to open the VBA editor. Go to
the Insert menu and choose Module. In that module, paste the
following code:


Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

To run the macro, switch back to Excel, press ALT+F8 and select
the macro from the list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



wrote in message
...
Not familar with this type of code. Is it VB code?
If so, how do I use it?

TIA
Bob

On Thu, 24 Mar 2005 14:45:14 -0600, "Chip Pearson"

wrote:

Bob,

Is the data really delimited by a | character? If so, use the
following code:


Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
Debug.Print F

I tried it.
This is what I did:
Highlighted the column that contains the data
pasted the code in as directed
got an error message Compile error sub or function not defined.
The word "Inkster" in the VB code was highlighted

Did I do something wrong?

TIA

Bob

On Thu, 24 Mar 2005 15:24:35 -0600, "Chip Pearson"
wrote:

Bob,

Yes, it is VBA code. Press ALT+F11 to open the VBA editor. Go to
the Insert menu and choose Module. In that module, paste the
following code:


Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

To run the macro, switch back to Excel, press ALT+F8 and select
the macro from the list.

I highlighted the column that has the data
Inserted the code below
ran the macro and got the following error
"Compile Error: sub or function not defined"
and he word Inkster in the VB code was highlighted.

Did I do something wrong?

Bob


On Thu, 24 Mar 2005 15:24:35 -0600, "Chip Pearson"
wrote:

Bob,

Yes, it is VBA code. Press ALT+F11 to open the VBA editor. Go to
the Insert menu and choose Module. In that module, paste the
following code:


Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

To run the macro, switch back to Excel, press ALT+F8 and select
the macro from the list.

Assuming that the data is separated by the | character you could just use
Data - Text to Columns and follow the wizard. You want to select delimited
and use the | character when it asks how the file is split.

Chips code is great and if you need an automated way of doing this then
follow his thread. If this is more of a one time or infrequent thing then
text to columns might just do the trick...

HTH

" wrote:

Hello
Windows XP Professional SP2
Office professional 2003 SP1

I have a column of data that has the following contents

contact 1 | contact2 | contact 3

I'd like to select the first contact. Each contact name is different
in size and usually there is a first and last name for each contact.
How do I extract to the first contact from this record?

Thank you
Bob

Bob

I think Chip meant InStr and not "Inkster"

Make the change and try again. Works for me.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 21:47:00 GMT, wrote:

I tried it.
This is what I did:
Highlighted the column that contains the data
pasted the code in as directed
got an error message Compile error sub or function not defined.
The word "Inkster" in the VB code was highlighted

Did I do something wrong?

TIA

Bob

On Thu, 24 Mar 2005 15:24:35 -0600, "Chip Pearson"
wrote:

Bob,

Yes, it is VBA code. Press ALT+F11 to open the VBA editor. Go to
the Insert menu and choose Module. In that module, paste the
following code:


Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

To run the macro, switch back to Excel, press ALT+F8 and select
the macro from the list.

Well, I know that I do not know much about VBA code, but I followed
the directions and made the change from Inkster to Instr and it still
doesn't work for me.

I get "Run-time error '5':
Invalid procedure call or arguement.

Just to clarify this is what the contents of one of the cells looks
like.
Ralph Stockhausen Jr| Cheryl Stockhausen | Dawn Noorlag

Thank you for your patience

Bob


On Thu, 24 Mar 2005 15:40:36 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Bob

I think Chip meant InStr and not "Inkster"

Make the change and try again. Works for me.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 21:47:00 GMT, wrote:

I tried it.
This is what I did:
Highlighted the column that contains the data
pasted the code in as directed
got an error message Compile error sub or function not defined.
The word "Inkster" in the VB code was highlighted

Did I do something wrong?

TIA

Bob

On Thu, 24 Mar 2005 15:24:35 -0600, "Chip Pearson"
wrote:

Bob,

Yes, it is VBA code. Press ALT+F11 to open the VBA editor. Go to
the Insert menu and choose Module. In that module, paste the
following code:


Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

To run the macro, switch back to Excel, press ALT+F8 and select
the macro from the list.

I put this in a general module

Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = InStr(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

and your string in Cell A1 of the active sheet.

When I ran it, it put up a msgbox with

Ralph Stockhausen Jr

in it.

--
Regards,
Tom Ogilvy

wrote in message
...
Well, I know that I do not know much about VBA code, but I followed
the directions and made the change from Inkster to Instr and it still
doesn't work for me.

I get "Run-time error '5':
Invalid procedure call or arguement.

Just to clarify this is what the contents of one of the cells looks
like.
Ralph Stockhausen Jr| Cheryl Stockhausen | Dawn Noorlag

Thank you for your patience

Bob


On Thu, 24 Mar 2005 15:40:36 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Bob

I think Chip meant InStr and not "Inkster"

Make the change and try again. Works for me.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 21:47:00 GMT, wrote:

I tried it.
This is what I did:
Highlighted the column that contains the data
pasted the code in as directed
got an error message Compile error sub or function not defined.
The word "Inkster" in the VB code was highlighted

Did I do something wrong?

TIA

Bob

On Thu, 24 Mar 2005 15:24:35 -0600, "Chip Pearson"
wrote:

Bob,

Yes, it is VBA code. Press ALT+F11 to open the VBA editor. Go to
the Insert menu and choose Module. In that module, paste the
following code:


Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = Inkster(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

To run the macro, switch back to Excel, press ALT+F8 and select
the macro from the list.

The column's that have this problem are C
and the first cell that needs this code is C2 thru C1009

Do I change this line to reflect the cell address that I want the VBA
code to run against? S = Range("A1").Text



Thanks


On Wed, 30 Mar 2005 13:39:21 -0500, "Tom Ogilvy"
wrote:

I put this in a general module

Sub AAA()
Dim S As String
Dim F As String
Dim Pos As Integer
S = Range("A1").Text
Pos = InStr(1, S, "|")
F = Left(S, Pos - 1)
MsgBox F
End Sub

and your string in Cell A1 of the active sheet.

When I ran it, it put up a msgbox with

Ralph Stockhausen Jr

in it.

Jim, thank you for your suggestion. I completely forgot about this
function. It worked well.

On Thu, 24 Mar 2005 14:15:03 -0800, "Jim Thomlinson"
wrote:

Assuming that the data is separated by the | character you could just use
Data - Text to Columns and follow the wizard. You want to select delimited
and use the | character when it asks how the file is split.

Chips code is great and if you need an automated way of doing this then
follow his thread. If this is more of a one time or infrequent thing then
text to columns might just do the trick...

HTH

" wrote:

Hello
Windows XP Professional SP2
Office professional 2003 SP1

I have a column of data that has the following contents

contact 1 | contact2 | contact 3

I'd like to select the first contact. Each contact name is different
in size and usually there is a first and last name for each contact.
How do I extract to the first contact from this record?

Thank you
Bob