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

 

[Jaafar Tribak]

Excel 2016 (Windows) 32 bit

	A	B
Ayr Utd	AYR Utd
Utc	UTC
Hull City	Hull City

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=CleanString(A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

Oops!

 

[Jaafar Tribak]

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

WBD

Well spotted wideboydixon !

I can't find the Like button on your post

 

[Rick Rothstein]

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"

With the code I have posted below, there is no need to do the above step... the code will work with the original all upper case letters (it will also work with the text as you have indicated you modified it above as well).

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"

If I have understood your requirements correctly (and there is no guarantee that I have), then the following code should work for you...

[table="width: 500"]
[tr]
	[td]Function CleanString(S As String) As String
  CleanString = Application.Proper(S)
  Mid(CleanString, 1, -InStr(S & " ", " ") * (InStr(S & " ", " ") <= 4)) = UCase(S)
End Function[/td]
[/tr]
[/table]