SOLVED

Iron Contributor

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

19 Replies

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

What exact logic shall be?

Remove specific character from the text;

Remove start of the text till first space;

Something else

# Re: How to returns a specific number of characters from a text string

More SUBSTITUTE().

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

# Re: How to returns a specific number of characters from a text string

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

best response confirmed by littlevillage (Iron Contributor)
Solution

# Re: How to returns a specific number of characters from a text string

``````=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,"")))``````

# Re: How to returns a specific number of characters from a text string

Thank you

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

# Re: How to returns a specific number of characters from a text string

Right here, in my office.

# Re: How to returns a specific number of characters from a text string

Hi Detlef,

Sorry for my english

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

# Re: How to returns a specific number of characters from a text string

Obviously I used Excel.

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

That is due to the formatting of the forum editor.

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

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

Then choose Language and enter the code.

# Re: How to returns a specific number of characters from a text string

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

# Re: How to returns a specific number of characters from a text string

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.

# Re: How to returns a specific number of characters from a text string

yes, my friend,

I see

Thank you

1 best response

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

# Re: How to returns a specific number of characters from a text string

``````=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,"")))``````