Extract Text after special character in a multi line Excel Cell

user_13296237 0
Viewed 239

I have multi line data in a cell (G3) that looks like

X: 1
Yen : Noodles
Amazon: 21cm

I want to extract all the data after the ":" symbol into cell F3. I have tries Split to text and then MID and CONCATENATE but not working because data in G3 should remain same. The values after ":" keep changing The return desired is 1 Noodles 21cm

excel-formula vba excel
2 Answers

With 2016:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,":","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b[2]")&" "&
 FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,":","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b[4]")&" "&
 FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,":","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b[6]")

With Office 365 or 2019 we can use TEXTJOIN:

=TEXTJOIN(" ",,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,":","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b["&{2,4,6}&"]"))

enter image description here

user_48515900

How about the User Defined Fuction:

Public Function Klean(s As String) As String
    Klean = ""
    arr = Split(s, Chr(10))
    
    For Each a In arr
        Klean = Klean & Mid(a, InStr(a, ":") + 1) & " "
     Next a
End Function

enter image description here

user_24746560
Related