Find proper case put delimiter after upper case

[shrinivasmj]

I have a data in A coloum , NEED FORMULA OR VB TO PUT DELIMITER BETWEEN UPPER CASE AND PROPER

2133 JOSEPH DAMON DR Magadan Miass (8)862
624 WOODLAND TRAILS DR Krasnoyarsk Kirsanov 4862
29 WASHINGTON ST. Magadan Cherepovets 8652
19441 134TH AVE. S.E. Tjumen Zavolzhye 84722

EG

NEED TO PUT THE DELIMATER ( ^ ) AS SHOWN BELOW

2133 JOSEPH DAMON DR ^ Magadan Miass (8)862
624 WOODLAND TRAILS DR ^ Krasnoyarsk Kirsanov 4862
29 WASHINGTON ST. ^ Magadan Cherepovets 8652
19441 134TH AVE. S.E. ^ Tjumen Zavolzhye 84722

 

[AliGW]

Re: Find proper case put dilimater afetr upper case

Are there always three groups of letters and/or numbers in the proper case section?

 

[shrinivasmj]

Re: Find proper case put dilimater afetr upper case

just put delimater after upper case ^

 

[AliGW]

Re: Find proper case put dilimater afetr upper case

just put delimater after upper case ^

This does NOT answer my question! I understand what you want to do. In order to be able to help, we need to know if the format of the text in proper case in your sample data will always be the same, i.e. three groups of letters/numbers, like Krasnoyarsk Kirsanov 4862.

 

[shrinivasmj]

Re: Find proper case put dilimater afetr upper case

yes same as seen

or else find proper case and put delimeter before it

 

[shrinivasmj]

Re: Find proper case put dilimater afetr upper case

waiting for replay

 

[shrinivasmj]

Re: Find proper case put dilimater afetr upper case

Sub MG14Dec48
Dim str As String, n As Long, nStr As String
str = [a1]
For n = Len([a1]) To 1 Step -1
If Mid(str, n, 1) = " " Then
If Mid(str, n - 1, 1) = UCase(Mid(str, n - 1, 1)) Then
nStr = Left(str, n - 1) & " { " & Right(str, Len(str) - n)
Exit For
End If
End If
Next n
Range("A2").Value = nStr
End Sub

ITworking for a2 cell only can u make it

 

[AliGW]

Re: Find proper case put dilimater afetr upper case

You should have said that you wanted a VBA solution in your thread title! Please be patient.

 

[mikerickson]

Re: Find proper case put dilimater afetr upper case

You could use this UDF.
FindCaseWord will find the first location of the first word that is in the indicated case.
Note that "1234" is in Proper Case. It is also in lower case and UPPER CASE. Use of the IgnoreNumerals argument.
Note also that "A123" is not a numeral and it is in both UPPER and Proper cases

Use the formula =REPLACE(A1,FIndCaseWord(3, A1),0,"^ ")

Function FindCaseWord(find_Case As VbStrConv, within_String As String, _
        Optional IgnoreNumerals As Boolean = True, Optional Delimiter As String = " ") As Long
    Rem 1 - upper case, 2 - lower case, 3 - proper case
    Dim i As Long
    Dim Words As Variant
    Dim Prefix As String
    Words = Split(within_String, Delimiter)
    For i = 0 To UBound(Words)
        If IgnoreNumerals And (LCase(Words(i)) = UCase(Words(i))) Then
            Prefix = Prefix & Delimiter & Words(i)
        ElseIf StrConv(Words(i), find_Case) = Words(i) Then
            FindCaseWord = Len(Prefix) + 1
            Exit For
        Else
            Prefix = Prefix & Delimiter & Words(i)
        End If
    Next i
End Function

 

[shrinivasmj]

Re: Find proper case put dilimater afetr upper case

FO BELOW DATA ITS NOT WORKING

1623 PINKNEY STREET Self-Employed 222

<tbody>
</tbody>