Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

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

Posted on • Edited 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 modeExit 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 modeExit fullscreen mode

Second method

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

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

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

Usetrim function to remove needless spaces

=TRIM(RIGHT(F2,LEN(F2)-FIND(",",F2)))
Enter fullscreen modeExit 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 theright 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 modeExit 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 tohttps://github.com/campelo/documentation and open a new pull request with your changes.

Top comments(0)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

Fullstack developer | Using dev.to to sharing my experiences, studies and curiosities with you.
  • Location
    Québec, Québec
  • Education
    Bachelor's degree in computer science
  • Work
    Software engineer, entrepreneur
  • Joined

More fromFlavio Campelo

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp