Organisaatiopääte sähköpostiosoitteesta

Excelissä on mahdollista yksinkertaisella kaavalla poimia talteen sähköpostiosoitteen organisaatiopääte. Tästä on hyötyä, jos halutaan suodattaa suurta sähköpostiosoitteistoa organisaatioittain esimerkiksi mainostustarkoituksessa ja käytössä on vain henkilöiden sähköpostiosoitteet.

Alla olevassa kuvassa näytetään, mihin kaava on tässä esimerkissä ajateltu sijoitettavaksi ja mihin sen viittaukset osoittavat.

Tässä esimerkissä käytetään muotoiltua taulukkoa. Katso tästä artikkeli, jossa esitellään ja opastetaan luomaan muotoiltu taulukko.

Esimerkin kaavan on tarkoitus poimia sähköpostiosoitteesta kaikki teksti, joka tulee @-merkin jälkeen. Kaavassa ensin ETSI-funktio hakee tiedon siitä, monesko merkki @ on käsiteltävässä sähköpostiosoitteessa. Sen jälkeen POIMI.TEKSTI antaa kaiken tekstin sähköpostiosoitteesta, joka tulee kyseisen merkin jälkeen:

=POIMI.TEKSTI( [@Sähköpostiosoite]; ETSI( “@”; [@Sähköpostiosoite]; 1) +1; 1000)

Kaavassa käytetään kahta funktiota:

  • ETSI antaa @-merkin sijainnin solun [@Sähköpostiosoite] tekstissä alkaen merkistä 1.
  • POIMI.TEKSTI antaa solusta [@Sähköpostiosoite] tekstiä, joka alkaa ETSI-funktion antamaa merkkiä seuraavasta merkistä (kaavan +1), enintään 1000 merkin verran. Luku 1000 voi olla mikä tahansa luku, joka varmasti kattaa pisimmänkin organisaatiopäätteen.

Näin henkilön nimi sekä @-merkki jäävät pois lopputuloksesta ja soluun jää ainoastaan sähköpostin pääte.

Lataa konellesi esimerkkitiedosto Organisaatiotunnus sähköpostista (.xlsx 12kt), niin voi testata kaavan toimintaa itse.

Kaava toimii myös muotoilemattomissa taulukoissa.

 


Caduseus Consulting avustaa ja kouluttaa monenlaisissa IT-tarpeissa!

Mikäli Excel-työ takkuilee ja haluatte ammattilaisapua jonkin projektin kanssa, voitte tilata asiantuntijapalvelua hintaan 45 €/h (+ alv). Kaikista toimeksiannoista voidaan antaa myös kiinteähintainen kokonaistarjous.

Katso myös hintalaskuri edullisille tilauskoulutuksille ja tilaa organisaatiollenne suosittu Excel helpoksi työelämässä-koulutus.


 
 

62 kommenttia.

  1. Вам перевод 196119 руб. https://tinyurl.com/gamNeavy NMVVcaduseus.fiRKKF

    elokuu 12, 2020 at 18:55

    Вам перевод 163191 руб. https://tinyurl.com/gamNeavy NMVVcaduseus.fiRKKF

    Reply
  2. Stefanie

    lokakuu 4, 2020 at 08:11

    Your style is very unique compared to other people I’ve read stuff from.
    Thanks for posting when you’ve got the opportunity, Guess
    I will just book mark this web site.

    Reply
  3. instagram private account viewer

    lokakuu 5, 2020 at 18:27

    Hello Dear, are you in fact visiting this web site regularly, if so after
    that you will definitely obtain pleasant know-how.

    Reply
  4. private instagram viewer

    lokakuu 5, 2020 at 19:59

    Wow! After all I got a webpage from where I be able to truly take valuable data concerning my study and
    knowledge.

    Reply
  5. instagram story viewer private account

    lokakuu 5, 2020 at 20:49

    I visited various websites however the audio feature for audio songs present at this web page is really marvelous.

    Reply
  6. view private instagram 2021

    lokakuu 5, 2020 at 21:20

    I’m amazed, I have to admit. Seldom do I come across a
    blog that’s equally educative and amusing, and let me tell you, you’ve hit the nail on the head.

    The issue is something that too few people are speaking intelligently about.
    I’m very happy I came across this during my search for something regarding
    this.

    Reply
  7. privateinstaviewer view private instagram profiles easily

    lokakuu 6, 2020 at 08:02

    Thank you for the good writeup. It in fact was a amusement account
    it. Look advanced to far added agreeable from
    you! However, how could we communicate?

    Reply
  8. instagram private profile viewer 2020

    lokakuu 6, 2020 at 09:04

    This website really has all the info I wanted concerning this subject and didn’t know who to ask.

    Reply
  9. private insta viewer

    lokakuu 6, 2020 at 10:02

    I pay a quick visit day-to-day some sites and information sites to read articles, except this weblog gives quality based
    articles.

    Reply
  10. instagram private profile viewer

    lokakuu 6, 2020 at 10:33

    Hello, I want to subscribe for this blog to obtain newest updates,
    thus where can i do it please help out.

    Reply
  11. private instagram story viewer

    lokakuu 6, 2020 at 11:06

    all the time i used to read smaller posts which also clear
    their motive, and that is also happening with this post which I am reading at this time.

    Reply
  12. privateinstaviewer view private instagram profiles easily

    lokakuu 6, 2020 at 11:26

    Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your blog?

    My website is in the very same area of interest as yours and my users would definitely benefit from a lot of the
    information you present here. Please let me know if this ok with you.
    Thank you!

    Reply
  13. privateinstaviewer view private instagram profiles easily

    lokakuu 6, 2020 at 11:30

    Everything is very open with a very clear explanation of the challenges.
    It was truly informative. Your site is very helpful.

    Thank you for sharing!

    Reply
  14. instagram private profile viewer

    lokakuu 6, 2020 at 13:28

    This web site really has all the info I wanted concerning this subject and didn’t know who to ask.

    Reply
  15. privateinstaviewer view private instagram profiles easily

    lokakuu 6, 2020 at 13:52

    Howdy very nice blog!! Guy .. Excellent .. Superb ..
    I will bookmark your blog and take the feeds additionally?
    I am happy to search out numerous helpful information right here
    in the put up, we’d like develop more strategies in this regard, thanks for sharing.
    . . . . .

    Reply
  16. insta private viewer

    lokakuu 6, 2020 at 14:01

    I want to to thank you for this excellent read!! I definitely loved
    every bit of it. I have got you saved as a favorite to check out new stuff you post…

    Reply
  17. instagram private profile viewer

    lokakuu 6, 2020 at 15:03

    Hello would you mind sharing which blog platform you’re using?
    I’m looking to start my own blog in the near future but I’m having a difficult
    time deciding between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design seems different then most blogs and I’m looking for something completely unique.
    P.S Sorry for being off-topic but I had to ask!

    Reply
  18. private insta viewer

    lokakuu 6, 2020 at 15:53

    Wow, this paragraph is fastidious, my sister is analyzing these
    things, thus I am going to let know her.

    Reply
  19. private instagram viewer

    lokakuu 6, 2020 at 16:14

    Your means of explaining everything in this article is genuinely good, all can without difficulty know it,
    Thanks a lot.

    Reply
  20. instagram private viewer

    lokakuu 6, 2020 at 17:36

    Hi to all, it’s actually a nice for me to pay a quick
    visit this web page, it consists of priceless Information.

    Reply
  21. private instaviewer

    lokakuu 6, 2020 at 17:36

    whoah this blog is excellent i like reading your articles.
    Stay up the great work! You recognize, a lot of individuals are searching
    around for this information, you could help them greatly.

    Reply
  22. instagram private profile viewer

    lokakuu 6, 2020 at 18:05

    Good post however I was wondering if you could write a
    litte more on this subject? I’d be very thankful if you could elaborate a little bit
    further. Many thanks!

    Reply
  23. private instagram viewer free

    lokakuu 6, 2020 at 18:30

    Hi there! I know this is somewhat off topic but I was
    wondering which blog platform are you using for this site?
    I’m getting fed up of WordPress because I’ve had issues with hackers
    and I’m looking at options for another platform. I would be great if you could point me in the direction of a good
    platform.

    Reply
  24. watchinsta

    lokakuu 6, 2020 at 21:12

    Hi Dear, are you truly visiting this web site daily,
    if so then you will without doubt get nice experience.

    Reply
  25. viewprivateprofiles

    lokakuu 7, 2020 at 02:08

    I am regular reader, how are you everybody? This article posted at this web page is
    truly fastidious.

    Reply
  26. private profile viewer

    lokakuu 7, 2020 at 04:03

    What’s up friends, how is everything, and what you
    want to say regarding this article, in my view its genuinely awesome in favor of me.

    Reply
  27. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 04:15

    My spouse and I stumbled over here from a different website and
    thought I should check things out. I like what I see so i am just following
    you. Look forward to exploring your web page again.

    Reply
  28. Larue

    lokakuu 7, 2020 at 04:17

    This post is really a good one it helps new the web visitors, who are wishing
    for blogging.

    Reply
  29. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 04:58

    An intriguing discussion is worth comment. There’s no doubt that that
    you ought to publish more about this topic, it might not be a
    taboo subject but generally folks don’t talk about
    these topics. To the next! Kind regards!!

    Reply
  30. private instagram viewer free

    lokakuu 7, 2020 at 05:22

    Greetings from Ohio! I’m bored to tears at work so I decided
    to check out your site on my iphone during lunch break. I really like the info you provide here and can’t wait to take a look when I get home.
    I’m surprised at how quick your blog loaded on my cell phone ..
    I’m not even using WIFI, just 3G .. Anyways, fantastic site!

    Reply
  31. instagram private profile viewer

    lokakuu 7, 2020 at 06:43

    Hey there I am so happy I found your site, I really found you by error, while I was searching on Digg for
    something else, Regardless I am here now and
    would just like to say thanks for a marvelous post and a
    all round exciting blog (I also love the theme/design), I don’t have time to browse it
    all at the minute but I have book-marked it and also added your RSS feeds, so when I have time I will be back
    to read a lot more, Please do keep up the awesome job.

    Reply
  32. ig private unlock

    lokakuu 7, 2020 at 07:03

    Hi, i think that i saw you visited my web site so i came to “return the favor”.I am attempting to
    find things to improve my website!I suppose its ok to use a few of your ideas!!

    Reply
  33. insta private viewer

    lokakuu 7, 2020 at 07:57

    WOW just what I was looking for. Came here by searching for private profile viewer

    Reply
  34. private instagram story viewer

    lokakuu 7, 2020 at 08:30

    Thanks for finally writing about > blog topic < Loved it!

    Reply
  35. private instagram viewer

    lokakuu 7, 2020 at 08:43

    I know this if off topic but I’m looking into starting my own weblog and
    was wondering what all is required to get setup? I’m assuming
    having a blog like yours would cost a pretty penny?

    I’m not very web savvy so I’m not 100% positive.
    Any recommendations or advice would be greatly appreciated.

    Many thanks

    Reply
  36. instagram private profile viewer 2020

    lokakuu 7, 2020 at 09:25

    I blog often and I seriously appreciate your information. This article has truly peaked my interest.
    I will book mark your site and keep checking for new details about once per week.
    I subscribed to your RSS feed as well.

    Reply
  37. instagram private profile viewer

    lokakuu 7, 2020 at 11:46

    You really make it seem really easy along with your presentation however I find this matter to be actually something that I
    feel I might by no means understand. It seems too complex and extremely extensive for me.
    I’m having a look forward in your subsequent put up, I’ll try to get the cling
    of it!

    Reply
  38. privateinstaviewer

    lokakuu 7, 2020 at 13:14

    I am regular visitor, how are you everybody?
    This article posted at this web site is in fact pleasant.

    Reply
  39. instagram private unlocker

    lokakuu 7, 2020 at 13:14

    Yes! Finally something about privateinstaviewer.

    Reply
  40. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 13:19

    great points altogether, you just won a brand new reader.
    What could you suggest about your publish that you
    just made some days in the past? Any sure?

    Reply
  41. instagram private viewer

    lokakuu 7, 2020 at 13:40

    If some one needs to be updated with hottest technologies afterward he must
    be pay a visit this web page and be up to date everyday.

    Reply
  42. private instagram viewer

    lokakuu 7, 2020 at 13:40

    Thanks to my father who stated to me on the topic of this
    web site, this web site is truly remarkable.

    Reply
  43. private profile viewer

    lokakuu 7, 2020 at 14:29

    Its like you learn my thoughts! You seem to understand a lot about this, such as you
    wrote the guide in it or something. I feel that you could do with a few %
    to force the message home a bit, but other than that, that is fantastic
    blog. A fantastic read. I will definitely be back.

    Reply
  44. instagram private account viewer

    lokakuu 7, 2020 at 17:13

    Post writing is also a excitement, if you know afterward you
    can write if not it is difficult to write.

    Reply
  45. instagram private account viewer

    lokakuu 7, 2020 at 18:13

    It’s very straightforward to find out any matter on net as compared to textbooks, as I found this piece of writing at this website.

    Reply
  46. private instagram viewer

    lokakuu 7, 2020 at 18:20

    You made some decent points there. I looked on the net for more information about the issue and found most individuals will go along with
    your views on this web site.

    Reply
  47. instagram private viewer

    lokakuu 7, 2020 at 18:42

    Hello, after reading this amazing article i am also happy to share my know-how here with
    colleagues.

    Reply
  48. private insta viewer

    lokakuu 7, 2020 at 21:53

    I am regular reader, how are you everybody? This paragraph posted at this site is
    actually good.

    Reply
  49. watchinsta

    lokakuu 7, 2020 at 23:10

    My programmer is trying to convince me to move to .net from PHP.
    I have always disliked the idea because of the costs.

    But he’s tryiong none the less. I’ve been using WordPress on numerous websites for about a
    year and am worried about switching to another
    platform. I have heard excellent things about blogengine.net.

    Is there a way I can import all my wordpress content into it?
    Any help would be greatly appreciated!

    Reply
  50. instagram private viewer

    lokakuu 8, 2020 at 03:53

    Useful information. Fortunate me I discovered your website accidentally, and I’m surprised why this twist of fate did not came
    about earlier! I bookmarked it.

    Reply

Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *