Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community
SOLVED

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

Iron Contributor

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 expect result
Thank you

small_village_1-1661095651787.png

 

 

19 Replies

@littlevillage 

=TRIM(SUBSTITUTE(C2,"x",""))

 

@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

small_village_0-1661097364945.png

@littlevillage 

What exact logic shall be?

Remove specific character from the text;

Remove start of the text till first space;

Something else

@littlevillage 

More SUBSTITUTE().

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

 

@Sergei Baklan 

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

small_village_0-1661119409437.png

 

best response confirmed by littlevillage (Iron Contributor)
Solution

@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,"")))

 

@Detlef Lewin 

Thank you

It is working perfectly, a little question please
Where have you create the formula?

@Detlef Lewin 

Hi Detlef,

Sorry for my english

I am just start with excel about 4 months

I mean, What did you use to write the formula?

 

@Detlef Lewin 

 

small_village_0-1661155655085.png

In regular excel I could not see something the same as this image, that's why I said' where did you write the formula"

@littlevillage 

That is due to the formatting of the forum editor.

 

@Detlef Lewin 

I see many member of this forum do it. How can I use it?

How do I ask questions here? Someone help me please. Where do I go to make posts?

@Josephine500 

on your right corner click it> go to Excel>Start a new Discussion

small_village_0-1661182436698.png

 

@littlevillage 

Open the full text editor and klick on "Insert code" in the icon bar.

TechCommunity Icon Bar.PNG

 

Then choose Language and enter the code.

TechCommunity Inaert Code.PNG

@Detlef Lewin 

sorry my friend, i am looking on google and youtube but i could not find how to open the full text Editor in excel.

@littlevillage 

That screenshot is not from Excel but from this forum.

In Excel you can expand the formula bar by pressing Ctrl+Shift+U.

And you can insert line breaks in a formula by pressing Alt+Enter.

@Hans Vogelaar 

yes, my friend,

I see 

Thank you

1 best response

Accepted Solutions
best response confirmed by littlevillage (Iron Contributor)
Solution

@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,"")))

 

View solution in original post