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

FO BELOW DATA ITS NOT WORKING

[TABLE="width: 491"]
<tbody>[TR]
[TD="width: 491"]1623 PINKNEY STREET Self-Employed 222[/TD]
[/TR]
</tbody>[/TABLE]

This is why I asked the question back in post #2, which you refused to answer! Please answer it now - what other formats could exist after the section in upper case?

 

[Eric W]

Re: Find proper case put dilimater afetr upper case

Here's a rather long formula:

	A	B
2133 JOSEPH DAMON DR Magadan Miass (8)862	2133 JOSEPH DAMON DR ^ Magadan Miass (8)862
624 WOODLAND TRAILS DR Krasnoyarsk Kirsanov 4862	624 WOODLAND TRAILS DR ^ Krasnoyarsk Kirsanov 4862
29 WASHINGTON ST. Magadan Cherepovets 8652	29 WASHINGTON ST. ^ Magadan Cherepovets 8652
19441 134TH AVE. S.E. Tjumen Zavolzhye 84722	19441 134TH AVE. S.E. ^ Tjumen Zavolzhye 84722
1623 PINKNEY STREET Self-Employed 222	1623 PINKNEY STREET ^ Self-Employed 222

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

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

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

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

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

</tbody>

Sheet15​

[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"]=TRIM(REPLACE(SUBSTITUTE(" "&A1," ",REPT(" ",200)),
AGGREGATE(15,3,FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},SUBSTITUTE(" "&A1," ",REPT(" ",200))),1)-100,1,"^"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The AGGREGATE function is available in Excel 2010 and later.

 

[Rick Rothstein]

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

Here is the same code I posted to your other thread which asked the same question except you asked for a curly bracket instead of a caret symbol (I changed the symbol in this new code to the caret)...

Sub InsertBracketAtEndOfUpperCase()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[a-z]" Then
        Data(R, 1) = Application.Replace(Data(R, 1), X - 1, 0, "^ ")
        Exit For
      End If
    Next
  Next
  Range("A2").Resize(UBound(Data)) = Data
End Sub

 

[Sunny Kow]

You can also give this a try

Function InsertQuote(rng As Range)
    Dim v As String, i As Long, x As Long, l As Long
    x = 0
    v = rng.Text
    l = Len(v)
    For i = 1 To l
        If Mid(v, i, 1) Like "[a-z]" Then
            x = i
            Exit For
        End If
    Next i
    InsertQuote = Trim(Left(rng, x - 2) & "^ " & Mid(rng, x - 1, 200))
    
End Function