Substitute formula lower and upper case character and long text problem

Hello Forum Leaders,

I have text in single column such as Text,Text with Numerical,semi colon,back slash and lower and upper case character.


In Column A2 as a long text. The Formula considered the Text two character only.

Another one problem The Formulas couldn’t considered some Texts (XXXXX)and Lower and Upper Case character Problems(XXX-xxx).

File attached for your reference.

Please help me.

JBeaucaire; Thank you so much for considered my querry.

QUERY 1

The following text in column A2.

; LOCATIONS/B/12/C ; LOCATIONS/B/13/C ; LOCATIONS/B/15/C ; LOCATIONS/B/18/B ; LOCATIONS/C/05/B ; LOCATIONS/C/06/C ; LOCATIONS/C/13/C ; LOCATIONS/C/19/C ; LOCATIONS/E/04/C ; LOCATIONS/E/06/C

I have apply the following formula in B2

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A37,"LOCATIONS",REPT(" ",99)),99,198)),"M-B",REPT(" ",99)),99)),"WAY",REPT(" ",99)),99)),"Received In",""),"/"," ")),";"," ",2),";"," ")," ","//"))," ","/"),"///"," "),"//","")

value return is first two text character only such as /B/12/C and B/13/C. But my required format is B/12/C B/13/C B/15/C B/18/B C/05/B C/06/C C/13/C C/19/C E/04/C E/06/C

Query 2.

The following text in column A2

; AI/// ; MP4-12C/Tralier/01/ ; LOCATIONS/Received In// ;LOCATIONS/A+Q Placing Way// ;LOCATIONS/Q/07/A ; LOCATIONS/Q/07/B ; LOCATIONS/Q/07/FLOOR

I have apply the following formula in B2. return the value is A+Q/Placing/Way. But My required format is Q/07/A Q/07/B Q/07/FLOOR.

Please help me.

Originally Posted by :) Sixthsense :)

Unable to post code in the body of this post because of securi blocking.

I had the same last week, by editing out various lines of the code I was posting, I found that the block was caused by nesting multiple levels of Replace.

I got around it by using this method in my code.

variable = Replace(original string, what, with)
variable = Replace(variable, what, with)

Just had another idea, test post using your code to see if it works.

** Code below credited to Sixthsense (see post #5)

Function DataMatch(r As Variant) As String
Dim sText As String, sRes() As String, vMatch As Variant, v As Variant, x As Long, sPref
sText = r

sText = Replace(sText, "LOCATIONS /", "LOCATIONS/", , , vbTextCompare) & " "
sPref = "LOCATIONS/"

With CreateObject("vbscript.regexp")
    .Pattern = sPref & "([a-z]*)/(\d{1,3})/([a-z]*( |;))"
    .MultiLine = False
    .Global = True
    .IgnoreCase = True
    If .test(sText) Then
        Set vMatch = .Execute(sText)
        For Each v In vMatch
            x = x + 1
            ReDim Preserve sRes(1 To x)
            sRes(x) = Replace( _
            Replace(v, sPref, "", , , vbTextCompare), ";", "", , , vbTextCompare)
        Next v
    End If
End With

DataMatch = Join(sRes, ",")

End Function

FlameRetired , Sixthsense and snb

Thank you so much for your help and suggestion.its a magic and Unbelievable.....