Jul 02 2020 07:41 AM
Escalation : RES26181649 : Urgent Help Needed- I need RES26181649 in one seperate column
Area-US, DC-Bangalore, RCA-Process-CSPO-TicketPrioritization- I need Bangalore in one column and US in another column
Jul 02 2020 08:08 AM
Please populate the few possible entries of raw data in column A and mock up the desired output in column B and then upload the Excel file here so that it would be easier for us to see the pattern and expected output.
Jul 02 2020 12:28 PM
If you are open to a VBA solution, you may create your own User Defined Function to get the desired sub string from the raw string.
This solution will only work if the desired sub string is preceded with either RES, Area or DC and these keywords are used in the User Defined Function given below.
Once the UDF is placed on a Standard Module, you may use it like a Regular Excel Function on the Worksheet.
You may use this UDF like...
=getSubString(A2,"res")
=getSubString(F2,"area")
=getSubString(F2,"dc")
To place this UDF into your workbook, follow these steps...
Please refer to the attached for more details.
The UDF is placed in yellow cells on the Sheet1.
UDF:
Function getSubString(ByVal str As String, crit As String) As String
Dim Matches As Object
With CreateObject("VBScript.RegExp")
.Global = True
If LCase(crit) = "res" Then
.Pattern = "(RES\d+)"
ElseIf LCase(crit) = "area" Then
.Pattern = "Area-([A-z\s]+)"
ElseIf LCase(crit) = "dc" Then
.Pattern = "DC-([A-z\s]+)"
Else
Exit Function
End If
If .Test(str) Then
Set Matches = .Execute(str)
getSubString = Matches(0).SubMatches(0)
End If
End With
End Function
Jul 02 2020 12:55 PM
In assumption that texts have the same structure, formula solution could be
=INDEX(FILTERXML("<r><n>" & SUBSTITUTE(A2," : ","</n><n>") & "</n></r>", "//n"),2)
and
=INDEX(
RIGHT(
TRANSPOSE(FILTERXML("<r><n>"&SUBSTITUTE(F2,",","</n><n>")&"</n></r>","//n")),
LEN(TRANSPOSE(FILTERXML("<r><n>"&SUBSTITUTE(F2,",","</n><n>")&"</n></r>","//n")))-
SEARCH("-",TRANSPOSE(FILTERXML("<r><n>"&SUBSTITUTE(F2,",","</n><n>")&"</n></r>","//n")))
),
{1,2})
Jul 02 2020 01:13 PM
Attached formula (added in J2:K3) may suffice, provided raw data field will always include "Area-", "DC-" & "RCA-" in that order...
It's basically a MID formula where the start position and character counts are based on the above mentioned constants, in the string.
Hope this helps.
Jul 02 2020 01:14 PM - edited Jul 02 2020 01:16 PM
@resham1985 Maybe I'm missing something but here are my 'low tech' solutions if it helps. The 3 output formulas are:
col B
=MID(A2,SEARCH("RES",A2),12)
col G
=MID(F2,6,SEARCH(", DC",F2)-6)
col H
=MID(F2,11+LEN(G2),SEARCH(", RCA",F2)-11-LEN(G2))
you can also look at the attached.
Jul 02 2020 11:44 PM
Jul 03 2020 09:45 AM
As variant
=LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH("SC - ",A2)+4,"")," -",";"),
SEARCH(";",SUBSTITUTE(REPLACE(A2,1,SEARCH("SC - ",A2)+4,"")," -",";"))-1
)
Jul 03 2020 09:47 AM
@resham1985 Here is the formula I used, which assumes the outcome you want follows "SC - " and is 3 characters or 2 characters followed by a ";". In you sample I assume and hope that your expected outcome on line 5 is a typo.
=SUBSTITUTE(MID($A2,SEARCH("SC -",$A2)+5,3),";","")
Hope is works and make sure to like the helpful posts and mark best solution to 'close' the thread.