Delete character after ':' but not delete the rest-CodePudding

For example my excel column is

CodeandPrice
3&12|4&200|2&
5&|2&
4&|
2&12|35&744

With & is separation between code and price, | is separation betweeen 2 item. I want to only get the code, so character before &.

CodeandPrice
3&|4&|2&
5&|2&
4&|
2&|35&

I ve googled but what I found is remove all character after/before. But what I want is, remove a character after & but not all, since there will be another code.

CodePudding user response：

For Excel 2010, maybe the easiest is to quickly throw together an UDF and invoke this as a function in your sheet, for example:

Function RegexReplace(s_in As String, pat As String, repl As String) As String

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = pat
    RegexReplace = .Replace(s_in, repl)
End With

End Function

Invoke like: =RegexReplace(A2,"\d \|","")

If one happens to have newer functionality available try:

Formula in B2:

=MAP(A2:A5,LAMBDA(a,TEXTJOIN("|",,TAKE(TEXTSPLIT(a,"&","|"),,1)&"&")))