By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,165 Members | 1,525 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,165 IT Pros & Developers. It's quick & easy.

A problem using mid and Instr.

P: n/a
Hi;

I am trying to extract a substring using a combination of the mid() and
Instr() i.e.

aString = "Jones, Thomas R, Dr."

hold = InStr(1, aString, " ," , 1)

newString = Mid(aString, 1, hold) + Mid(aString, hold + 2, InStr(hold + 1,
aString, " ", 1))

I want to trim the middle initial and title and keep only last name, comma
and first name.

Any suggestions as to what I am doing wrong here ? Mid$(aString, hold + 2,
InStr(hold + 1, aString, " ", 1))

There is probably some method of the string object that I need to use
instead of those string functions. Right ?

Thanks,
--
Gordon
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Gordon,

When it is not about millions of names because it is slower (not terrible
however it is), than I would probably in your case use the split with a
delimited string.

http://msdn.microsoft.com/library/de...SplitTopic.asp

The two first (or probably the first and the thirth) in the array are than
what you need.

I hope this helps,

Cor
Nov 21 '05 #2

P: n/a
Hi,

Dim s as string = "Jones, Thomas R, Dr."

dim strComma() as string = s.split(","c)

dim strSpace() as string = strComma(1).Trim.Split(" "c)

Debug.Writeline(strComma(0) & ", " & strSpace(0))

Ken
---------------------------

"Gordon" wrote:
Hi;

I am trying to extract a substring using a combination of the mid() and
Instr() i.e.

aString = "Jones, Thomas R, Dr."

hold = InStr(1, aString, " ," , 1)

newString = Mid(aString, 1, hold) + Mid(aString, hold + 2, InStr(hold + 1,
aString, " ", 1))

I want to trim the middle initial and title and keep only last name, comma
and first name.

Any suggestions as to what I am doing wrong here ? Mid$(aString, hold + 2,
InStr(hold + 1, aString, " ", 1))

There is probably some method of the string object that I need to use
instead of those string functions. Right ?

Thanks,
--
Gordon

Nov 21 '05 #3

P: n/a
Ken,

I had the idea that the result would have to be Thomas Jones.

By the way, what is in the USA the first name.
Ken
Kenneth
Kenneth X

By us it is
Cor = Firstname
Cornelis = official firstname or Christianname (seldom used as firstname or
it should be more foreign names)
Cornelis Johannes Marinus = All firstnames (can be one until whatever
amount)

Cor
Nov 21 '05 #4

P: n/a
Thanks for your suggestions.



"Gordon" wrote:
Hi;

I am trying to extract a substring using a combination of the mid() and
Instr() i.e.

aString = "Jones, Thomas R, Dr."

hold = InStr(1, aString, " ," , 1)

newString = Mid(aString, 1, hold) + Mid(aString, hold + 2, InStr(hold + 1,
aString, " ", 1))

I want to trim the middle initial and title and keep only last name, comma
and first name.

Any suggestions as to what I am doing wrong here ? Mid$(aString, hold + 2,
InStr(hold + 1, aString, " ", 1))

There is probably some method of the string object that I need to use
instead of those string functions. Right ?

Thanks,
--
Gordon

Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.