Forum Discussion

Copper Contributor

May 25, 2022

Manipulate text between delimiters

How can I covert following: 78756||23456||12345 To: 787||234||123 I want the first 3 digits

OliverScheurich

Gold Contributor

May 25, 2022

=LEFT(A1,3)&"||"&MID(A1,FIND("||",A1)+2,3)&"||"&MID(A1,FIND("||",A1,FIND("||",A1)+1)+2,3)

Maybe with this formula.

text between demiliters.xlsx9 KB

apei78759
Copper Contributor
May 25, 2022
Thank you! This works for string for 3 entries: 78756||23456||12345

But how about something that works regardless of the number of entries. For example, 5

78756||23456||12345||34567||09685
- Detlef_Lewin
  Silver Contributor
  May 25, 2022
  apei78759
  Try this.
  =TEXTJOIN("||",TRUE,LEFT(FILTERXML("<y><z>"&SUBSTITUTE(A1,"||","</z><z>")&"</z></y>","//z"),3))
  - SergeiBaklan
    Diamond Contributor
    May 25, 2022
    Detlef_Lewin
    
    Exactly the same with Beta function
    
    =TEXTJOIN("||", 1, LEFT( TEXTSPLIT(A1, "||"), 3) )

Resources