DEV Community

Cover image for Excel - How to get a substring from a text
Flavio Campelo
Flavio Campelo

Posted on • Updated on

Excel - How to get a substring from a text

📮 Contact 🇧🇷 🇺🇸 🇫🇷

Twitter
LinkedIn


If you're trying to get a part of a text from a excel's cell, this post can help you with a few samples.
For all examples we need a delimited char to split our text in two parts, nedeed and needless.

Text on the left

For this example, the comma , is our delimited char. Now, you can eliminate all right side (needless text).

=FIND(G2,FIND(",",G2)-1)
Enter fullscreen mode Exit fullscreen mode

Image 1

Text on the right

For this example, the comma , is our delimited char.

First method

=TRIM(RIGHT(F2,LEN(F2)-LOOKUP("@",SUBSTITUE(F2,",","@",LEN(F2)-LEN(SUBSTITUE(F2,",",""))),1)))
Enter fullscreen mode Exit fullscreen mode

Second method

=FIND(",",F2)
Enter fullscreen mode Exit fullscreen mode

Get all right text (needed text) from that char.

=RIGHT(F2,LEN(F2)-FIND(",",F2))
Enter fullscreen mode Exit fullscreen mode

Use trim function to remove needless spaces

=TRIM(RIGHT(F2,LEN(F2)-FIND(",",F2)))
Enter fullscreen mode Exit fullscreen mode

Image 3

Text on the middle

For that, you have to combine both of previous actions (left + right).
Image 4

First of all you could eliminate the right needless part of the text.

Then we will eliminate all left side from searched text. On our example, we will use slash char / to split our text.

=RIGHT(F2,LEN(F2)-FIND("@",SUBSTITUTE(F2,"/","@",LEN(F2)-LEN(SUBSTITUTE(F2,"/",""))),1))
Enter fullscreen mode Exit fullscreen mode

Image 2

Sources:

Substring

Getting the last position of a character using excel formula

Typos or suggestions?

If you've found a typo, a sentence that could be improved or anything else that should be updated on this blog post, you can access it through a git repository and make a pull request. If you feel comfortable with github, instead of posting a comment, please go directly to https://github.com/campelo/documentation and open a new pull request with your changes.

Discussion (0)