Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Aug 21, 2022
Solved

How to returns a specific number of characters from a text string

Hi, I want to returns a specific number of characters from a text string and I try to use MID and SEARCH formula for first row, but I dont know what to do next for other rows. How can we get the e...
excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Aug 21, 2022

    littlevillage 

    =LET(
a,FILTERXML("<y><z>"&SUBSTITUTE(C2," ","</z><z>")&"</z></y>","//z"),
b,--(LEN(a)={11.12.18}),
c,SEQUENCE(3,,1,0),
d,MMULT(b,c),
CONCAT(IF(d,a,"")))

     

  • littlevillage's avatar
    littlevillage
    Iron Contributor
    Aug 21, 2022

    Detlef_Lewin 

    Thank you for your response

    when the first and at the end of text string added characters differently "x". It's show the result in column B

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Aug 21, 2022

      littlevillage 

      More SUBSTITUTE().

      =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"x",""),"a",""),"b",""),"c",""))

       

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Aug 21, 2022

      littlevillage 

      What exact logic shall be?

      Remove specific character from the text;

      Remove start of the text till first space;

      Something else

      • littlevillage's avatar
        littlevillage
        Iron Contributor
        Aug 21, 2022

        SergeiBaklan 

        I want to separate the tracking number from the text string and its length11, 12 or 18 characters
        sometimes it's at the beginning, middle or end of the string text

        the expect result shown in column A

         

Resources