Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub test()
Dim i As Long
Dim s As String
Dim sArr() As String
s = "Dr. med. Betty Eastwood"
sArr = Split(s, " ")
If UBound(sArr) = 0 Then
For i = 0 To UBound(sArr)
Debug.Print sArr(i)
Next
End If
End Sub

Regards,
Peter T

"andreashermle" wrote in message
...
Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

On Nov 26, 6:24*pm, "Peter T" <peter_t@discussions wrote:
Sub test()
Dim i As Long
Dim s As String
Dim sArr() As String
* * s = "Dr. med. Betty Eastwood"
* * sArr = Split(s, " ")
* * If UBound(sArr) = 0 Then
* * * * For i = 0 To UBound(sArr)
* * * * * * Debug.Print sArr(i)
* * * * Next
* * End If
End Sub

Regards,
Peter T

"andreashermle" wrote in message

...



Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Hide quoted text -

- Show quoted text -

Hi Peter,

thank you very much for your swift answer. I am afraid to tell you
that the macro is not working. Any idea why.

Regards, Andreas

"andreashermle" wrote in message
...
On Nov 26, 6:24 pm, "Peter T" <peter_t@discussions wrote:
Sub test()
Dim i As Long
Dim s As String
Dim sArr() As String
s = "Dr. med. Betty Eastwood"
sArr = Split(s, " ")
If UBound(sArr) = 0 Then
For i = 0 To UBound(sArr)
Debug.Print sArr(i)
Next
End If
End Sub

Regards,
Peter T

"andreashermle" wrote in message

...



Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Hide quoted text -

- Show quoted text -

Hi Peter,

thank you very much for your swift answer. I am afraid to tell you
that the macro is not working. Any idea why.

Regards, Andreas

================================================

What does not working mean? Eg it does not return anything in the Immediate
window, gives wrong results, it breaks on an error, etc

Regards,
Peter T

On Thu, 26 Nov 2009 09:03:34 -0800 (PST), andreashermle
wrote:

Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

You can extract each word into a separate column by using the
Data/Text-to-columns wizard with <space as the separator.

Since this is the programming group, you could also write a UDF or a macro to
do the same.

That would result, for example:

A1: Dr. med. Betty Eastwood
B1: Dr.
C1: med.
D1: Betty
E1: Eastwood.

That is what you are asking, but is that really what you want? I think not
since the titles and names won't line up. But unless you are more specific ...

Here is a simple UDF that will extract any given word, with the word sequence
given by the Index argument.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ExtrWord($A1,Index)

in some cell.

==================================
Option Explicit
Function ExtrWord(s As String, Index As Long) As String
Dim sTemp As Variant
sTemp = Split(s, " ")
If Index <= UBound(sTemp) + 1 Then
ExtrWord = sTemp(Index - 1)
End If
End Function
===============================




==============================
Option Explicit
Function ExtrWord(s As String, Index As Long) As String
Dim sTemp As Variant
sTemp = Split(s, " ")
If Index <= UBound(sTemp) + 1 Then
ExtrWord = sTemp(Index - 1)
End If
End Function
=========================

--ron

On Nov 26, 11:23*pm, "Peter T" <peter_t@discussions wrote:
"andreashermle" wrote in message

...
On Nov 26, 6:24 pm, "Peter T" <peter_t@discussions wrote:





Sub test()
Dim i As Long
Dim s As String
Dim sArr() As String
s = "Dr. med. Betty Eastwood"
sArr = Split(s, " ")
If UBound(sArr) = 0 Then
For i = 0 To UBound(sArr)
Debug.Print sArr(i)
Next
End If
End Sub

Regards,
Peter T

"andreashermle" wrote in message

....

Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Hide quoted text -

- Show quoted text -

Hi Peter,

thank you very much for your swift answer. I am afraid to tell you
that the macro is not working. Any idea why.

Regards, Andreas

================================================

What does not working mean? Eg it does not return anything in the Immediate
window, gives wrong results, it breaks on an error, etc

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Dear Peter,

I got it running now. Great job! Thank you very much for your
professional help. Regards, Andreas

On Nov 27, 12:39*am, Ron Rosenfeld wrote:
On Thu, 26 Nov 2009 09:03:34 -0800 (PST), andreashermle
wrote:





Dear Experts:

I got a client list (German names) where the whole name including
title is in just one cell, such as

Dr. med. Betty Eastwood
Prof. Dr. Dr. Joe Doe
Dr. John Westwood
Dr. phil. Robert Friedman

I know how to extract the first occurrence of a word and the last one,
i.e. in the first example "Dr." and "Eastwood". But how to extract the
ones that are in between (one to three occurrences).

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

You can extract each word into a separate column by using the
Data/Text-to-columns wizard with <space as the separator.

Since this is the programming group, you could also write a UDF or a macro to
do the same.

That would result, for example:

A1: * * Dr. med. Betty Eastwood
B1: * * Dr.
C1: * * med.
D1: * * Betty
E1: * * Eastwood. * * *

That is what you are asking, but is that really what you want? *I think not
since the titles and names won't line up. *But unless you are more specific ...

Here is a simple UDF that will extract any given word, with the word sequence
given by the Index argument.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ExtrWord($A1,Index)

*in some cell.

==================================
Option Explicit
Function ExtrWord(s As String, Index As Long) As String
*Dim sTemp As Variant
*sTemp = Split(s, " ")
*If Index <= UBound(sTemp) + 1 Then
* * ExtrWord = sTemp(Index - 1)
*End If
End Function
===============================

==============================
Option Explicit
Function ExtrWord(s As String, Index As Long) As String
*Dim sTemp As Variant
*sTemp = Split(s, " ")
*If Index <= UBound(sTemp) + 1 Then
* * ExtrWord = sTemp(Index - 1)
*End If
End Function
=========================

--ron- Hide quoted text -

- Show quoted text -

Dear Ron,

thank you very much for your professional help. Very good job. Thank
you. Regards, Andreas