Substring-need help

J

Johnny

Hello everyone

I need help with string!
I have for ex. the string "Tomorrow will be a nice day" and I want to
remove "will be a" in order to get final string "Tomorrow nice day".
Could someone help?

Tks in advance
 
P

Peter Doering

Johnny said:
I have for ex. the string "Tomorrow will be a nice day" and I want to
remove "will be a" in order to get final string "Tomorrow nice day".
Could someone help?
Click to expand...

Debug.Print Replace("Tomorrow will be a nice day", "will be a", "")
 
B

Beetle

Try the Replace function

Replace("Tomorrow will be a nice day", "will be a", "")

would return "Tomorrow nice day"
 
J

Johnny

Tks both for answering

The string I gave was just an exemple, the string I'm working with,
will contain people names, so each query row have a different person
name.
The objective is, if a person name has more then 35 characteres I
wanto to remove middle names until strName is 35 characaters long.
I have names like this:

John xxxxxx xxxxxx xxxxxx xxxxxxxx Smith (Example)

Need to remove some middle names until 35 characters long


regards
 
J

John W. Vinson

Tks both for answering

The string I gave was just an exemple, the string I'm working with,
will contain people names, so each query row have a different person
name.
The objective is, if a person name has more then 35 characteres I
wanto to remove middle names until strName is 35 characaters long.
I have names like this:

John xxxxxx xxxxxx xxxxxx xxxxxxxx Smith (Example)

Need to remove some middle names until 35 characters long


regards
Click to expand...

What if you have a name like Venkataraghavan Srinivasarianshivamantan?

You'll need a VBA function to do this. Air code, untested:

Public Function RemoveMiddleName(strName as String) As String
Dim AllNames() As String
Dim iPos As Integer
Dim Newname As String
If Len(strName) <= 35 Then
RemoveMiddleName = strName
Exit Function
Else
AllNames = Split(strName, " ") ' generate an array of names
If uBound(AllNames) = 0 Then
MsgBox "Only one name left and it's too long!", vbOKOnly
RemoveMiddleName = AllNames(0)
Else
NewName = AllNames(0) ' get first name
For iPos = 2 to UBound(AllNames)
NewName = NewName & " " & AllNames(iPos)
Next iPos
' recursively call the function to trim out another name if needed
If Len(NewName) <= 35 Then
RemoveMiddleName = NewName
Else
RemoveMiddleName = RemoveMiddleName(NewName)
End If
End If
End Sub
 
J

Johnny

Hi John

Thank you very much for your help.
It´s quit impossible that someone in my country will have such big
names, but your function fits my needs.
What I really thought was to keep 1st, 2nd and last name when ever
complete name is bigger then 35 chars.
Would it be possible?
Johnny
 
J

John W. Vinson

Hi John

Thank you very much for your help.
It´s quit impossible that someone in my country will have such big
names, but your function fits my needs.
What I really thought was to keep 1st, 2nd and last name when ever
complete name is bigger then 35 chars.
Would it be possible?
Johnny
Click to expand...

Use

AllNames(0) & " " & AllNames(1) & " " & AllNames(UBound(AllNames))
 
L

Larry Linson

Johnny said:
It´s quit impossible that someone in my country
will have such big names, but your function fits
my needs.
Click to expand...

That's interesting. Does that imply you live in a country that doesn't have
people of different heritage in the population? John is (and I am) from the
U.S., and while such names are not common here, there is a substantial
(East) Indian population so you have to plan on them. Do no East Indians
come to your country?
What I really thought was to keep 1st, 2nd and
last name when ever complete name is bigger
then 35 chars.
Click to expand...

So what do you do when 1st, 2nd, and last exceed 35 characters? Is that
really what you want if the full name is made up of 12 names each of 7
characters? 1st, 2nd, 3rd, 4th, and last would fit within the limit, unless
you have to count the blank spaces between the names.
Would it be possible?
Click to expand...

Certainly, unless the name were Featherstonehaugh Witherspoon
Featherstonehaugh. Featherstonehaugh, by the way, is an actual surname from
the British Isles, and surnames are often given as first names, too. (Just
for the record, it is pronounced "Fanshaw".)

Larry Linson
Microsoft Office Access MVP
 
J

Johnny

Hi Larry

I understand your point of view, but in fact the application I'm
trying to develop is a non comercial application, almost home user,
and deals with a restricted number of people, any foreign people, and
in accordance with country namming rules no such names are expected.

tks
 
J

John W. Vinson

Hi Larry

I understand your point of view, but in fact the application I'm
trying to develop is a non comercial application, almost home user,
and deals with a restricted number of people, any foreign people, and
in accordance with country namming rules no such names are expected.

tks
Click to expand...

Just out of curiosity - how many people do you expect with a) more than two
middle names and b) with a full name longer than 35 characters? I expect this
routine will almost never actually run!
 
J

Johnny

Hello John Vinson

First of all, I'm asking for help because I'm newby on access, but I
like it very much and also the VBA I intent to learn more deeply
because since a long time I like programming but never had time to
seat and study, and also found this newsgroups recentlly, and saw that
are enough expertise here for me to learn with all this knowledge.
Concerning your questions.
A) I have two middle names and a lot of people here has 3 and four and
sometimes more middle name, I should say that aroud 95% of people on
my DB have at least two middle names, what is very common here, and a
lot fo them have more than two.
B)My name, including spaces has 25 characteres, and yes a lot of
people have 35 caharacteres of more. My wife's name has 39 characteres
and there isn't at all a long name here :).
You know, since 1143 till 1910 the government was a monarchy, and
kings and queens had a lot of names, those were the roots of namming
conventions here.

Regards
Johnny
 
J

John W. Vinson

A) I have two middle names and a lot of people here has 3 and four and
sometimes more middle name, I should say that aroud 95% of people on
my DB have at least two middle names, what is very common here, and a
lot fo them have more than two.
B)My name, including spaces has 25 characteres, and yes a lot of
people have 35 caharacteres of more. My wife's name has 39 characteres
and there isn't at all a long name here :).
You know, since 1143 till 1910 the government was a monarchy, and
kings and queens had a lot of names, those were the roots of namming
conventions here.
Click to expand...

Fascinating. You've never said where "here" is (or I've missed it) - Western
Europe somewhere I'm guessing?

Glad you got the problem solved, I hope to your satisfaction!
 
J

Johnny

Hello

In fact I didn't mention where "here" is.
I'm from Portugal on Iberian Peninsula, Western Europe.

Have a nice day and thank you for your support!

Johnny
 
Top