DEV Community

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

Posted on • Edited on

3 3

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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs