Returning only the uppercase letters in string

kalle82 · Feb 26, 2010

Im using VBA with word. Im fetching data via a search made on our internal page. As im not allowed to connect it to the mainframe im stuck with using the source code the searches provides. I have managed parse most of it.

But now I have a problem I can´t seem to solve..

When searching the database the string my parsing returns can have two formats.

First it can consist of a name made up of ONLY uppercase letters, this name have any position.

1. CARL Kristian Thor
2. Sture BERIT Stefan
3. Arne Pelle GÖSTA
4. Bengt PÄR
5. MOHAMMED Achmed

For this case i would like to transform the string into only the name made up of the uppercase letter i.e
1. CARL
2. BERIT
3. GÖSTA
4. PÄR
5. MOHAMMED

Secondly it can return, any given number of namnes but all names consist of uppercase.

1. Carl Kristian Thor
2. Sture berit Stefan
3. Arne Pelle Gösta
4. Bengt Pär
5. Mohammaed Achmed

For this case I want it as it is.

The code used for extracting this is

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")

ie.Visible = False

ie.Navigate "

https://una5pkg.rsv.se/na/na_personsok/personsok.do?idPersSelect="

& TextBox3.Value

start = 0
StopTime = 20000000
While start < StopTime
start = start + 1
Wend

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

Here i want a statement to change namnet into one of the two i described above.

Hope this isnt to troubling,

Cheers Carl

mintjulep · Feb 26, 2010

Loop through each character in the string. Check if it's ASCII value is in the range for capital letters. If so, return it, if not on to the next character.

kalle82 · Feb 26, 2010

How do i do that?

Im not that good yet..

MarkWalsh · Feb 26, 2010

That won't work, mintJulep. In the case of:

CARL Kristian Thor

you will end up with:

CARLKT

The best route for this for me would be to use regular expressions, but that is most likely beyond your experience at this point.

kalle82 · Feb 26, 2010

Yes, can you point me in a acertain direction.. I came here to get to know hot to not to be told I cant do it..

PHV · Feb 26, 2010

What about this ?

Code:

...
namnet = Mid(C, startnamn, slutnamn - startnamn + 1)
Dim strResult
Dim booUpper As Boolean
booUpper = False
For Each strResult In Split(namnet)
  If Not (strResult Like "*[a-z]*") Then
    booUpper = True
    Exit For
  End If
Next
If booUpper Then namnet = strResult

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

kalle82 · Feb 26, 2010

Work like a Charm! But when i search for people stated in teh second string i.e 1. Carl Kristian Thor, i dont get anything in my textfield?

Sub getnavetinfo()

Dim numTDs
Dim C

Dim namnet
Dim startnamn
Dim slutnamn

ie.Visible = False

ie.Navigate "

https://una5pkg.rsv.se/na/na_personsok/personsok.do?idPersSelect="

& TextBox3.Value

start = 0
StopTime = 20000000
While start < StopTime
start = start + 1
Wend

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

Dim strResult
Dim booUpper As Boolean
booUpper = False
For Each strResult In Split(namnet)
If Not (strResult Like "*[a-z]*") Then
booUpper = True
Exit For
End If
Next

If booUpper Then namnet = strResult

TextBox18.Value = namnet

kalle82 · Feb 26, 2010

When I search and the string returned does not contain a uppcase name i.e "Carl Kristian Thor", nothings shows in my textfield?

In the case no or noone of the names are made upp of only uppercase letters, I want it to write all fo the string, i.e. textbox15 = Carl Kristian Thor.

It´s really really confusing and the help I have been given so far is awesome thanks PHV!

SkipVought · Feb 26, 2010

In the case no or noone of the names are made upp of only uppercase letters, I want it to write all fo the string, i.e. textbox15 = Carl Kristian Thor.

Then, you have to program it that way.

Code:

If booUpper Then
  namnet = strResult
else
  namnet = x  'whatever you want here
end if

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

PHV · Feb 26, 2010

i.e. textbox15 = Carl Kristian Thor
So you wanted this ?

Code:

If booUpper Then
  TextBox1[!]8[/!].Value = strResult
Else
  TextBox1[!]5[/!].Value = namnet
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

kalle82 · Feb 26, 2010

I tried that?!?

ie.Visible = False

ie.Navigate "

https://una5pkg.rsv.se/na/na_personsok/personsok.do?idPersSelect="

& TextBox3.Value

start = 0
StopTime = 20000000
While start < StopTime
start = start + 1
Wend

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

Dim strResult
Dim booUpper As Boolean
booUpper = False
For Each strResult In Split(namnet)
If Not (strResult Like "*[a-z]*") Then
booUpper = True
Exit For
End If
Next

If booUpper Then

namnet = strResult

TextBox18.Value = namnet

Else

namnet2 = Mid(C, startnamn, slutnamn - startnamn + 1)

TextBox18.Value = namnet2

End If

Works for everything else but nothing shows in the text box when search return a string like Carl Kristian Thor

kalle82 · Feb 26, 2010

Okay to clarify

String contains: CARL Kristian Thor

Using the this code Textbox value = CARL
namnet = Mid(C, startnamn, slutnamn - startnamn + 1)
namnet2 = Mid(C, startnamn, slutnamn - startnamn + 1)
Dim strResult
Dim booUpper As Boolean
booUpper = False
For Each strResult In Split(namnet)
If Not (strResult Like "*[a-z]*") Then
booUpper = True
Exit For
End If
Next
If booUpper Then
namnet = strResult
TextBox18.Value = namnet
Else
TextBox18.Value = namnet2
End If

String contains: Patricia

Using the this code Textbox value = ""(nothing at all not even a blank space)

String contains: CARL Kristian Thor

Using the this code Textbox value = CARL
namnet = Mid(C, startnamn, slutnamn - startnamn + 1)
namnet2 = Mid(C, startnamn, slutnamn - startnamn + 1)
Dim strResult
Dim booUpper As Boolean
booUpper = False
For Each strResult In Split(namnet)
If Not (strResult Like "*[a-z]*") Then
booUpper = True
Exit For
End If
Next
If booUpper Then
namnet = strResult
TextBox18.Value = namnet
Else
TextBox18.Value = namnet2
End If

string contains: CARL Kristian Thor
using this code: Textbox value is "Carl Kristian Thor"

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

textbox18.value = namnet

String contains: Patricia
Using this code textboxvalue is "Patricia"

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

textbox18.value = namnet

PHV · Feb 26, 2010

My suggestion was this:

Code:

C = ie.Document.body.innerHTML
startnamn = InStr(1, C, "rnamn:") + 28
slutnamn = InStr(1, C, "Mellannamn:") - 37
namnet = Mid(C, startnamn, slutnamn - startnamn + 1)
Dim strResult
Dim booUpper As Boolean
booUpper = False
For Each strResult In Split(namnet)
  If Not (strResult Like "*[a-z]*") Then
    booUpper = True
    Exit For
  End If
Next
If booUpper Then namnet = strResult
TextBox18.Value = namnet

But I think you even didn't need an extra boolean variable:

Code:

...
Dim strResult
For Each strResult In Split(namnet)
  If Not (strResult Like "*[a-z]*") Then Exit For
Next
If Not(IsEmpty(strResult)) Then namnet = strResult
TextBox18.Value = namnet

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

SkipVought · Feb 26, 2010

Code:

Dim strResult
Dim booUpper As Boolean

namnet =  "CARL Kristian Thor"

booUpper = False
For Each strResult In Split(namnet)
If Not (strResult Like "*[a-z]*") Then
   booUpper = True
    Exit For
  End If
Next
If booUpper Then
TextBox18.Value = strResult
Else
TextBox18.Value = namnet
End If

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

kalle82 · Feb 26, 2010

Guys I'm really sorry for not getting the hang of this, your help is really invaluable.

The code you provided works FINE, when translating "CARL Kristina Thor" --> CARL, which is correct

But everytime I do a search that generates a string containing names "Patricia Märta Louise", or just one name "Patricia", it gives me back nothing....

Okay by chance I found out the following. This is my sub, if you go to the end above the "ie.quit"

you see that i put in namnet in the textbox another time, when including this i get it to work with "Patrica" showing up, but string CARL Kristian Thor --> does not transform into CARL

When not including i.e 'TextBox18.Value = namnet

Transformation of CARL Kristian Thor works fine --> CARL

But string Patricia --> "nothing"

Can this be a timing issue, that the program can´t handle that?

Code:

Sub getnavetinfo()

Dim numTDs
Dim C

Dim namnet
Dim namnet2
Dim startnamn
Dim slutnamn


Dim mellannamnet
Dim mellannamnet2
Dim startmellannamn
Dim slutmellanamn

Dim efternamnet
Dim startefternamn
Dim slutefternamn

Dim adressen
Dim adressen2
Dim startadress
Dim slutadress

Dim sarskilda
Dim sarskilda2
Dim startsarskilda
Dim slutsarskilda

Dim fastigheten
Dim startfastighet
Dim slutfastighet

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")


ie.Visible = False

ie.Navigate "[URL unfurl="true"]https://una5pkg.rsv.se/na/na_personsok/personsok.do?idPersSelect="[/URL] & TextBox3.Value

start = 0
 StopTime = 50000000
  While start < StopTime
       start = start + 1
    Wend

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML
  
startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37
   
Dim strResult
Dim booUpper As Boolean

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

booUpper = False
For Each strResult In Split(namnet)
If Not (strResult Like "*[a-z]*") Then
   booUpper = True
    Exit For
  End If
Next
If booUpper Then
TextBox18.Value = strResult
Else
TextBox18.Value = namnet
End If


startmellannamn = InStr(1, C, "Mellannamn:") + 33

slutmellannamn = InStr(1, C, "Efternamn:") - 37
    
mellannamnet = Mid(C, startmellannamn, slutmellannamn - startmellannamn + 1)

If mellannamnet = "&nbsp; " Then

mellannamnet2 = Replace(mellannamnet, "&nbsp;", "Har inget mellannamn!")

Else

mellannamnet2 = mellannamnet

End If

startefternamn = InStr(1, C, "Efternamn:") + 32
 
slutefternamn = InStr(1, C, "Aviseringsnamn:") - 37

efternamnet = Mid(C, startefternamn, slutefternamn - startefternamn + 1)
   
startfastighet = InStr(1, C, "Fastighet:") + 32

slutfastighet = InStr(1, C, "ringsadress:") - 100

fastigheten = Mid(C, startfastighet, slutfastighet - startfastighet + 1)

startadress = InStr(1, C, "ringsadress:") + 34

slutadress = InStr(1, C, "rskild postadress") - 92

adressen = Mid(C, startadress, slutadress - startadress + 1)
   
adressen2 = Replace(adressen, "<BR>", vbNewLine)
   
startsarskild = InStr(1, C, "rskild postadress") + 40
slutsarskild = InStr(1, C, "Civilst") - 264

sarskilda = Mid(C, startsarskild, slutsarskild - startsarskild + 1)

If sarskilda = "&nbsp; " Then

TextBox23.Value = "Har ingen särskild postadress!"

Else

sarskilda = Replace(sarskilda, "<BR>", vbNewLine)
   
TextBox23.Value = sarskilda

End If
 
TextBox18.Value = namnet

TextBox22.Value = mellannamnet2

TextBox19.Value = efternamnet

TextBox5.Value = adressen2

TextBox20.Value = fastigheten

ie.Quit

Set ie = Nothing

End Sub

SkipVought · Feb 26, 2010

The code I previously posted DOES EXACTLY THAT.

if

namnet = "Patricia Märta Louise"

then

the textbox will contain

Patricia Märta Louise

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

kalle82 · Feb 26, 2010

Yeah i tried it skip it really does!

Now i got a an error 5?

It's like there is a timing issue?

When searching for ordinary names with just one uppercase letter, the textbox is blank... And even more wierd is that that the code works in fetching the data when using the old method..?

Any1 have a clue about this?

SkipVought · Feb 26, 2010

No timeing issue.

What code are you currently using that produces the error?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

kalle82 · Feb 26, 2010

I use the code posted above... I callvthis when i press à nitton in My userform.

SkipVought · Feb 26, 2010

Code:

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

EXACTLY what value is in C?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Returning only the uppercase letters in string

Technical User

Technical User

Technical User

Programmer

Technical User

MIS

Technical User

Technical User

Programmer

MIS

Technical User

Technical User

MIS

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor