Cleaning strings

[fredrerik84]

Hi Excel community.

I need a little more help cleaning some strings.

Basically I have a variable called Ateam which gets its info from a webpage.

Originally the text was in all uppercase, I have made some changes so that every word gets capital letter like this:

Ateam = "Ayr Utd" or Ateam = "Utc"

However I need a little help compiling a code so that is the first word has either 2 or 3 letters its should capitalize all words in first words. So the desired reults get ruturned like this:

Ateam "AYR Utd" / "UTC"

and if first word exceeds 3 words like hull city this code should do nothing ,

Ateam = "Hull City"

Hope some brilliant excel wizard could help a little

 

[wideboydixon]

Excel 2016 (Windows) 32 bit
	A	B
1	Ayr Utd	AYR Utd
2	Utc	UTC
3	Hull City	Hull City
Sheet1

Cell Formulas
Range	Formula
B1	=CleanString(A1)

Public Function CleanString(inputString As String) As String

Dim spacePos As Long

CleanString = inputString
If Len(Split(inputString, " ")(0)) > 3 Then Exit Function
spacePos = InStr(1, CleanString, " ")
If spacePos > 0 Then
    CleanString = UCase$(Left$(CleanString, spacePos - 1)) & Mid$(CleanString, spacePos)
Else
    CleanString = UCase$(CleanString)
End If

End Function

WBD

 

[Jaafar Tribak]

Another function:

Function foo(ByVal s As String) As String
    Dim sFirstWord As String

    s = WorksheetFunction.Proper(s)
    sFirstWord = Left(s, InStr(s, " ") - 1)
    
    If Len(sFirstWord) = 2 Or Len(sFirstWord) = 3 Then
       s = Replace(s, sFirstWord, UCase(sFirstWord))
    End If
    foo = s

End Function

 

[fredrerik84]

Hi thanks to the both of you. for your help. While I was waiting I built this code:
but your solution is probably better

            Result = Split(Ateam, " ")(0)
            Resultcount = Len(Result)
            If Resultcount < 4 Then
               Ateam = UCase(Left(Ateam, 3)) & LCase(Mid(Ateam, 4))
            End If

 

[Peter_SSs]

While I was waiting I built this code:

Wouldn't your code produce "AYR utd" not the desired "AYR Utd" as you stated in post #1?

 

[wideboydixon]

Another function:

This doesn't work for entries without a space or for entries that may have content like "Ayr Ayrton Senna"

WBD

 

[wideboydixon]

A bit shorter:

Public Function CleanString(inputString As String) As String

Dim spacePos As Long

CleanString = inputString
If Len(Split(CleanString, " ")(0)) > 3 Then Exit Function
spacePos = InStr(1, CleanString, " ")
If spacePos = 0 Then spacePos = Len(CleanString) + 1
CleanString = UCase$(Left$(CleanString, spacePos - 1)) & Mid$(CleanString, spacePos)

End Function

WBD

 

[Peter_SSs]

Perhaps you could do the whole conversion from the original upper case to the desired result with this slight variation snippet:

Dim pos As Long

Ateam = Application.Proper(Ateam)
pos = InStr(1, Ateam & " ", " ")
If pos < 5 Then Ateam = UCase(Left(Ateam, pos - 1)) & Mid(Ateam, pos)

 

[fredrerik84]

Wouldn't your code produce "AYR utd" not the desired "AYR Utd" as you stated in post #1?

- You are indeed correct sir, I solved it with a function to get uppercase for 2nd word. but my code is very messy. maybe ill try this as you have sugested:

Dim pos As Long

Ateam = Application.Proper(Ateam)
pos = InStr(1, Ateam & " ", " ")
If pos < 5 Then Ateam = UCase(Left(Ateam, pos - 1)) & Mid(Ateam, pos)

 

[fredrerik84]

public Function CleanString(inputString As String) As String

Dim spacePos As Long

CleanString = inputString
If Len(Split(CleanString, " ")(0)) > 3 Then Exit Function
spacePos = InStr(1, CleanString, " ")
If spacePos = 0 Then spacePos = Len(CleanString) + 1
CleanString = UCase$(Left$(CleanString, spacePos - 1)) & Mid$(CleanString, spacePos)

End Function

This function is also a good option

Thanks to help for your time and effort to giving me new ideas and better options as my own codes are not the best