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.

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

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