need to find the text length of 10 character CAPS AND NUMBER and put ^ delimiter BEFORE AND AFTER

[shrinivasmj]

i need formula OR VB CODE to put ^ delimiter to the text below

EG--TEXT IN EXCEL = Comprehensive Insurance CI25874567 4000 1 Year richard Pendergast 101 Delia St


OUTPUT ---Comprehensive Insurance ^CI25874567^ 4000 1 Year richard Pendergast 101 Delia St

 

[PCL]

Assuming value in B4
Somewhere put
=REPLACE(REPLACE(B4;FIND(" ";B4;FIND(" ";B4;1)+1);1;" ^");FIND(" ";B4;FIND(" ";B4;FIND(" ";B4;1)+1)+1);1;"^ ")

 

[pike]

or vba

Option Explicit
Sub LookandReplace()
    Dim rngCell As Range, t As Long
    For Each rngCell In Range("E1:E20")
        If rngCell Like "* [A-Z][A-Z]######## *" Then
            For t = 1 To Len(rngCell)
                If Mid(rngCell.Value, t, 12) Like " [A-Z][A-Z]######## " Then
                    rngCell.Value = Replace(rngCell.Value, Mid(rngCell.Value, t, 12), " ^" & Mid(rngCell.Value, t + 1, 10) & "^ ")
                    Exit For
                End If
            Next
        End If
    Next
End Sub

 

[Peter_SSs]

1. Is this really just a repeat of your other question only now looking for 10 characters instead of 8?

2. As I tried to indicate to you in that other thread, one example does not give helpers much to go on. Surely you could give 5-10 examples to make it clearer?

3. I also asked in that thread for you detail where the results should go - the same applies here. Do they go in another column (which one?) or do they replace the original data or something else?

4. Can you explain why you are inserting the delimiters? If is is so that you can subsequently do some other action (eg Text to Columns) it would be useful to know as there may be a more convenient way to go straight to those results.

5. In any case, here is a sample code that looks at data in column A (you haven't told us where your data is) and puts the results in column B. I have assumed from your other thread that the item in question simply needs to be any combination of upper case letters & digits, though you haven't told us that this time and your one example has 2 upper case letters followed by 8 digits so perhaps that is the case?

Sub AddDelimiter()
  Dim c As Range
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "\b([A-Z0-9]{10})\b"
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If .test(c.Value) Then c.Offset(, 1).Value = .Replace(c.Value, "^$1^")
    Next c
  End With
End Sub