h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

I need to split a value from a recordset into two separate fields at the last space. For example:

I have a FullName field in my recordset:

"David John Bailey"

I want to split into into Forename and Surname fields at the last space:

"David John"
"Bailey"

How do I do this using ASP/VBScript? I know it has something to do with the InStr command, but can't find any books which explain this. Can anyone help?

B.

Try using the InStrRev function to locate the last space in the string, then the Mid and Left function to return the end and beginning.

spacepos = InStrRev(name, " ")
firstnames = Left(name, spacepos -1)
lastnames = Mid(name, spacepos + 1)

You can use Trim() to shear off any leading or traling spaces if you want

FM

Thanks MF!

It worked perfectly. The 10000 records were re-imported into a new database and cleaned up with only 10 record glitches... easily fixed.

Thanks so much.

B.


PS: Found this online a couple of minutes ago:

http://www.w3schools.com/vbscript/default.asp