Select Specific character from a column and paste the related value to another Column

Question

Working on a 2016 version excel file where i need to copy only the specific characters which contains _ or - from a column and paste the related data into another column.

If any of the row contains "|" then remove everything after this symbol.

Input Data:

col1                                                  col2
Disconnected on B_NKLA_TUV_0022 As Per 
Roll DCN-BVLR-OCR-NKLT | Flapping
VAAP CLR_ NLTK_BKC_ASSO CBR Thing
VCR-DCW- NKL-NKJ-MLP | CBR-XDL-BHG
Disconnected on B_NFA_SUV_0025 As

Steps: First we need to remove all the character after "|" from col1 Then Exctract characters containing "" OR "-", If there is any space between 2 sentance containing "" OR "-", We need to combibne it and paste into col2

Output Data:

col1                                                  col2
Disconnected on B_NKLA_TUV_0022 As Per             B_NKLA_TUV_0022
Roll DCN-BVLR-OCR-NKLT | Flapping                  DCN-BVLR-OCR-NKLT
VAAP CLR_ NLTK_BKC_ASSO CBR Thing                  CLR_ NLTK_BKC_ASSO
Disconnected on 23 Dec 2022
VCR-DCW- NKL-NKJ-MLP | CBR-XDL-BHG                 VCR-DCW- NKL-NKJ-MLP
Disconnected on B_NFA_SUV_0025 As                  B_NFA_SUV_0025

Need Suggestion on it.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function — braX, May 03 '23 at 08:43

score 3 · Answer 1 · answered May 03 '23 at 09:09

3

Try the following formula-

=TEXTJOIN(" ",1,
IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(TEXTBEFORE(A2,"|",,,,A2)," ","</s><s>")&"</s></t>",
"//s[contains(., '_') or contains(., '-')]"),""))

answered May 03 '23 at 09:09

Harun24hr

30,391
4
21
36

Thanks for the update , However i have tried but getting #NAME? as value in col 2. – Manz May 03 '23 at 10:01
What is your excel version? This solution will only work on `Office365`. – Harun24hr May 03 '23 at 10:03
I amusing Excel 2016, Please Suggest the change to be made . – Manz May 03 '23 at 10:09
Harun24hr - For Excel 2016 version, Please Suggest the change that needs to be made in the above line of script. – Manz May 03 '23 at 10:22

JvdV · Answer 2 · 2023-05-03T14:25:44.743

Excel 2016 - Formula:

One could try FILTERXML() and use a simple predicate in the xpath expression to filter the correct substring^‡:

Formula in B2:

=IFERROR(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("|",A1&"|")),"- ","-|"),"_ ","_|")," ","</s><s>")&"</s></t>","//s[translate(.,'-_','')!=.]"),"|"," "),"")

^‡_{You can be much more restrictive if need be with xpath. This is done through anding more predicates. More information here.}

PowerQuery:

Alternatively, you could load your table into PQ. Follow the steps described here to add a custom function based on a regular expression and invoke this using the following regular expression:

^[^|]*?(\\b[A-Z0-9]+(?:[-_] ?[A-Z0-9]+)+)\\b.*$|.

Replace this with a backreference to the 1st capture group $1. Now you can, as per given link, simply replace error values with a NULL if so desired.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnRegexExtr", each fnRegexExtr([Col1], "^[^|]*?(\\b[A-Z0-9]+(?:[-_] ?[A-Z0-9]+)+)\\b.*$|.", "$1")),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Invoked Custom Function", {{"fnRegexExtr", null}})
in
    #"Replaced Errors"

VBA:

Another alternative is creating your own custom User Defined Function (UDF) through VBA, and invoke this like a formula on your sheet. For example:

Public Function RegexReplace(s As String, pat As String, rep As String, Optional cse As Boolean = False) As String

Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")

RE.Pattern = pat
RE.IgnoreCase = cse
RegexReplace = RE.Replace(s, rep)

End Function

Now invoke this through using a slightly different pattern:

=RegexReplace(A2,"^[^|]*?(\b[A-Z0-9]+(?:[-_] ?[A-Z0-9]+)+)\b.*$|.+","$1")

Thanks for the Answer. Do we have any option in Excel 2016 formula where we can keep withspaces between substrings — Manz, May 03 '23 at 14:03

Select Specific character from a column and paste the related value to another Column

2 Answers2