Have you read either one of my last two messages???Sorry This is it:
Cell A: f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227
Cell B (with function): ISALIA ZARRASON
Have you read either one of my last two messages???
Give this UDF a try...How can I get from the string:
f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227
Los Cerritos?
That part of text always come with the first letter in upper case
Function ProperCaseWords(S As String) As String
Dim X As Long, TempText As String, Words() As String
TempText = S
For X = 1 To Len(TempText)
If Mid(TempText, X, 1) Like "[!A-Za-z ]" Then Mid(TempText, X) = " "
Next
Words = Split(Application.Trim(TempText))
For X = 0 To UBound(Words)
If Words(X) <> StrConv(Words(X), vbProperCase) Or Len(Words(X)) = 1 Then Words(X) = ""
Next
ProperCaseWords = Application.Trim(Join(Words))
End Function
Give this UDF a try...
Code:Function ProperCaseWords(S As String) As String Dim X As Long, TempText As String, Words() As String TempText = S For X = 1 To Len(TempText) If Mid(TempText, X, 1) Like "[!A-Za-z ]" Then Mid(TempText, X) = " " Next Words = Split(Application.Trim(TempText)) For X = 0 To UBound(Words) If Words(X) <> StrConv(Words(X), vbProperCase) Or Len(Words(X)) = 1 Then Words(X) = "" Next ProperCaseWords = Application.Trim(Join(Words)) End Function
It worked perfect
Again Thank You Rick
It worked perfect. Again Thank You Rick
This is the UDF (user defined function) that I came up with...
Code:Function UpperCaseWords(S As String) As String Dim X As Long, TempText As String TempText = " " & S & " " For X = 2 To Len(TempText) - 1 If Mid(TempText, X, 1) Like "[!A-Z ]" Or Mid(TempText, X - 1, 3) Like "[!A-Z][A-Z][!A-Z]" Then Mid(TempText, X) = " " End If Next UpperCaseWords = Application.Trim(TempText) End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use UpperCaseWords just like it was a built-in Excel function. For example,
=UpperCaseWords(A1)