Extract text after space 1

[mcquam]

I though this would be relatively simple but can't find any reference to it.

I have a field containing full name and want to extract the surname which is always after the last space and is of variable length. However, there can be any number of spaces.

CR9 and access db.

 

[dgillz]

What happens when you have names such as:

"Billy Bob Thornton"
"Jean-Claude Van Damme"

How do you want to handle this?


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[mcquam]

I'm ok with just the word after the last space even if it splits a name.

 

[Madawc]

Treat the elements as separate fields, Split({name}, " ")[1] and so on.

For an address like MR O SMITH, "MR" would be element [1], "O" would be element [2], "SMITH" would be element [3] and element [4] would be null. Put them in formula fields and adjust them there. Remember that formulas stop when they hit a null, unless ISNULL is used to test.

You can also use UBOUND to find the number of table elements. The number given by UNBOUND should be the number of the element with the surname. Put UNBOUND in a formula field called @NameElements. Surname should then be

Split({name}, " ")[@NameElements]

If that fails you could still get the surname, by a formula field that tests @NameElements for the different possible values and selects the correct element accordingly.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[dgillz]

Instrrev({Yourfield}," ") will return an integer that tells you where the last space in your string is found.

In the case of "W C Fields" it returns a 4.

You need to trim your field in case it has any trailing spaces. Combine this with the mid function as follows:

mid({YourField},Instrrev(trim({YourField})," ")+1)



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[mcquam]

Brilliant! Thanks.