Separating a string

[Mark]

I am using Word 97 and I want to be able to pickup a
person surname from their full name which has previously
been stored with a string entitled sName.

Can someone assist me please

Thanks

Mark

 

[Jay Freedman]

I am using Word 97 and I want to be able to pickup a
person surname from their full name which has previously
been stored with a string entitled sName.

Hi, Mark,

You'll need to supply some more details:

- In sName, is the surname stored at the beginning or at the end?
- Is there some unique character such as a comma between the parts of
the name, or are they separated with ordinary spaces?
- How can you tell whether the surname has one, two, three or more
words (John Smith, John von Schmidt, John van der Linden)?

 

[Guest]

The surname is at the end of the name, the names are
generally separated with spaces. I don't think there are
many names tht are double barrelled, there may be one or
two surnames which are hyphenated.

Mark

 

[Helmut Weber]

Hi Mark,
this might may help you,
but without rules on what is in "aName",
there can be no perfect solution.
Dim aName As String
Dim bName As String
aName = "Smith, John-John"
bName = Right(aName, Len(aName) - InStr(aName, " "))
MsgBox "[" & bName & "]"
Brackets for testing in oder to detect
leading or trailing blanks.

Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"

 

[Martin Seelhofer]

MsgBox "[" & bName & "]"

Brackets for testing in oder to detect
leading or trailing blanks.

You can always use the Trim-function to get rid of leading
or trailing blanks:

LTrim(...) removes leading blanks (*left* trim)
RTrim(...) removes trailing blanks (*right* trim)
Trim(...) removes both


Cheers,

Martin

 

[Helmut Weber]

Hi Martin,
thank you for the advice, but I would like to see
in the messagebox whether the preceeding
routine did, what I was expecting. Simply trimming
blanks doesn't tell me, whether there was something
to trim at all.
Greetings from bavaria.
Helmut Weber

 

[Martin Seelhofer]

Hi Helmut

[...] Simply trimming blanks doesn't tell me, whether there
was something to trim at all.

So, why don't you just compare the "untrimmed" string length
to the trimmed one?

bName = Right(aName, Len(aName) - InStr(aName, " "))
uLen = Len(bName)
bName = Trim(bName)
tLen = Len(bName)
If uLen > tLen Then
...

Could also do that with LTrim or RTrim to get the number of leading/
trailing blanks...


Just an idea ;-) Cheers,

Martin