Search for Certain Part of String

[Giri]

Hi All,

Currently in an excel spreadsheet I have values within cells such as:

"WIE - Infrastructure Equity Fund"

Is there anyway to save ONLY the last part of the above value in an array or something?

i.e only save "Infrastructure Equity Fund"

Thank you for your help!!

Kind Regards,

Giri

[Bob Phillips]

Try
=RIGHT(A2,LEN(A2)-(FIND(" - ",A2)+2))

[Kenneth Hobs]

[VBA]Sub t()
Dim s As String, a() As String
s = "WIE - Infrastructure Equity Fund"
Range("A1").Value2 = s
a() = Split(Range("A1").Value2, " - ")
If UBound(a) = 1 Then MsgBox a(UBound(a))
End Sub[/VBA]

[Giri]

xld and Kenneth, Thanks so much for the replies.

Kenneth, I had never used the "Split" command before - it's working perfectly!!

xld, thanks as well - however I didn't quite understand how your method works exactly.

Giri

[Paul_Hossler]

Ken -- As a very minor footnote, if there's 2 dashes

s = "WIE - Infrastructure Equity - Fund"

The test will fail and return the wrong thing. Probably not a problem

Paul

[Giri]

Hi Guys,

I have come across a problem that I'm not too sure on how to approach.

I have one column with cells containing names in the following format "John Doe", "Jane", "Steve Barney Smith". I am trying to split these names up as one word and then use that to search another column. However, I am not sure how to write a macro for this because there are a different number of spaces between each word in each cell... nothing is constant.

Is there a way to perhaps ... sort cells by the number of words in the cell or something? That may be a starting point if possible..

If anyone has any ideas about how to go about doing this, I would greatly appreciate it. I'm sure my colleague at work would be very thankful too lol!

Kind Regards,

Giri

[Bob Phillips]

Tricky. You could split by last space, so John Peter Morris is split as John Peter and Morris. But Iain St John would then split as Iain St and John when it should be Iain and St John.

You need some rules.

[Kenneth Hobs]

While this may be somewhat related to your first post, I would suggest that next time that you start a new thread and add a link to a thread if it relates.

As xld said, when dealing with names, you will need special rules for those exception cases. I guess that we should know those but not only do you have St John you could have St John Jr. or St John III and so on. This is why it is prudent to have Last Name column or field rather than Name.

To combine this need with your previous request, I would use a UDF. The concept is similar to the @Field function in Quattro Pro.

If you have a string like "Steve - Barney Smith" in A7 and want the Barney Smith, you can do this: =field(A7,,"-")

If your string was that above with not "-" then it would return the whole string.

If you have the string "John Peter Morris" in A2 and wanted the last word then =field(A2,3), or better yet, =field(A2).

[vba]Function Field(s As String, Optional FieldNumber As Long = 0, _
Optional Delimiter As String = " ")
Dim sArray() As String
sArray = Split(s, Delimiter)
On Error Resume Next
If FieldNumber < 1 Then FieldNumber = UBound(sArray) + 1
Field = sArray(FieldNumber - 1)
End Function[/vba]