Skip i'r prif gynnwys

Fformiwla Excel: Tynnwch yr enw cyntaf a'r enw olaf o'r cyfeiriad e-bost

Awdur: Haul Wedi'i Addasu Diwethaf: 2020-03-05

Mae'r tiwtorial hwn yn cyflwyno'r fformwlâu i dynnu enw cyntaf ac enw olaf o gyfeiriadau e-bost penodol i mewn i gelloedd ar wahân fel y dangosir isod, hefyd, mae'n darparu enghraifft i egluro dadleuon a rhannau'r fformwlâu.
estyniad dyfyniad doc o enw ffeil 5

Detholiad enw cyntaf

Fformiwla generig:

LEFT(e_address,FIND("separator",)-1)

Dadleuon

E_address: the email address you want to extract the first name.
Separator: the delimiter between first name and last name.

Sut mae'r fformiwla hon yn gweithio

I dynnu enw cyntaf o'r cyfeiriad e-bost yng nghell B3, defnyddiwch y fformiwla isod:

=LEFT(B3,FIND("_",B3)-1)

Pwyswch Rhowch allweddol.
echdynnu doc ​​e-bost enw olaf cyntaf 2

Esboniad

FIND swyddogaeth: dod o hyd i safle cychwyn cymeriad neu destun mewn llinyn testun penodol. Y fformiwla DERBYN ("_", B3) yn darganfod lleoliad cymeriad “_” yn y gell B3, yn dychwelyd 4.

LEFT swyddogaeth: echdynnu testun o ochr chwith y llinyn testun a roddir yn seiliedig ar hyd y testun a roddir. Fformiwla L.EFT (B3, FIND ("_", B3) -1) gellir ei ystyried yn CHWITH (B3,4-1), tynnwch y 3 nod cyntaf o'r testun yng nghell B3.

Detholiad enw olaf

Fformiwla generig

LEFT(RIGHT(e_address,LEN(e_address)-FIND("separacter",e_address)),FIND("@",RIGHT(e_address,LEN(e_address)-FIND("separator",e_address)))-1)

Dadl

E_address: the email address you want to extract the first name.
Separator: the delimiter between first name and last name.

Sut mae'r fformiwla hon yn gweithio

I dynnu enw olaf o'r cyfeiriad e-bost yng nghell B3, defnyddiwch y fformiwla isod:

=LEFT(RIGHT(B3,LEN(B3)-FIND("_",B3)),FIND("@",RIGHT(B3,LEN(B3)-FIND("_",B3)))-1)

Pwyswch Rhowch allweddol.
echdynnu doc ​​e-bost enw olaf cyntaf 3

Esboniad

FIND swyddogaeth: dod o hyd i safle cychwyn cymeriad neu destun mewn llinyn testun penodol.

LEN swyddogaeth: cyfrif rhif llinyn testun.

RIGHT swyddogaeth: i dynnu testun o'r ochr dde.

Y fformiwla DDE (B3, LEN (B3) -FIND ("_", B3)) gellir ei weld fel:
= DDE (B3, LEN (B3) -4)
= DDE (B3,17)
=""

yna DEWCH ("@", DDE (B3, LEN (B3) -FIND ("_", B3))) gellir ei ystyried yn
= FIND ("@",,"")
= "6"

LEFT swyddogaeth: echdynnu testun o ochr chwith y llinyn testun a roddir yn seiliedig ar hyd y testun a roddir. Y fformiwla LEFT(RIGHT(B3,LEN(B3)-FIND("_",B3)),FIND("@",RIGHT(B3,LEN(B3)-FIND("_",B3)))-1) i'w gweld fel y rhain:
=LEFT(RIGHT(B3,LEN(B3)-FIND("_",B3)),6-1)
=LEFT ("",5)
= "Smith"

Sylw

Os ydych chi am dynnu enw cyntaf ac enw olaf gyda llythyren gyntaf, gallwch ychwanegu'r swyddogaeth PROPER o flaen y fformwlâu uchod, fel:

= EIDDO (CHWITH (B3, DERBYN ("_", B3) -1))
=PROPER(LEFT(RIGHT(B3,LEN(B3)-FIND("_",B3)),FIND("@",RIGHT(B3,LEN(B3)-FIND("_",B3)))-1))
echdynnu doc ​​e-bost enw olaf cyntaf 4

Ffeil Sampl

sampl docCliciwch i lawrlwytho ffeil sampl

Fformiwlâu Perthynas

Swyddogaethau Perthynas

Yr Offer Cynhyrchedd Swyddfa Gorau

Kutools for Excel - Yn Eich Helpu i Sefyll Allan O Dyrfa

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau  |  Dileu Rhesi Gwag  |  Cyfuno Colofnau neu Gelloedd heb Colli Data  |  Rownd heb Fformiwla ...
Super VLookup: Meini Prawf Lluosog  |  Gwerth Lluosog  |  Ar draws Aml-Daflenni  |  Edrych Niwlog...
Adv. Rhestr gwympo: Rhestr Gollwng Hawdd  |  Rhestr Gollwng Dibynnol  |  Rhestr Gollwng Aml-ddewis...
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharer Colofnau i Dewiswch Yr Un Celloedd a Gwahanol ...
Nodweddion dan Sylw: Ffocws ar y Grid  |  Golwg Dylunio  |  Bar Fformiwla Mawr  |  Rheolwr Llyfr Gwaith a Thaflen | Llyfrgell Adnoddau (Testun Auto)  |  Dewiswr Dyddiad  |  Cyfuno Taflenni Gwaith  |  Amgryptio/Dadgryptio Celloedd  |  Anfon E-byst trwy Restr  |  Hidlo Super  |  Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau ...)  |  50 + Siart Mathau (Siart Gantt ...)  |  40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd ...)  |  19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr ...)  |  12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred ...)  |  7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Rhannwch Celloedd Excel ...)  |  ... a mwy

Kutools ar gyfer Excel Mae ganddo Dros 300 o Nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...

Disgrifiad

Tab Office - Galluogi Darllen a Golygu Tabiau yn Microsoft Office (gan gynnwys Excel)

  • Un eiliad i newid rhwng dwsinau o ddogfennau agored!
  • Gostyngwch gannoedd o gliciau llygoden i chi bob dydd, ffarweliwch â llaw llygoden.
  • Yn cynyddu eich cynhyrchiant 50% wrth wylio a golygu sawl dogfen.
  • Yn dod â Thabiau Effeithlon i'r Swyddfa (gan gynnwys Excel), Just Like Chrome, Edge a Firefox.
Comments (5)
Rated 5 out of 5 · 1 ratings
Jay Chow
Jay Chow
This comment was minimized by the moderator on the site 
<body><table border="1" cellspacing="0" cellpadding="4">
  <tbody style="font-family:sans-serif">
    <tr bgcolor="#999999" style="color:#ffffff;">
      <td>Email Address</td>
      <td>First Name</td>
      <td>Last Name</td>
      <td>Domain</td>
      <td>Separator</td>
    </tr>
    <tr>
      <td></td>
      <td>Jane</td>
      <td>Doe</td>
      <td>abc.com</td>
      <td>“_”</td>
    </tr>
    <tr bgcolor="#eeeeee">
      <td></td>
      <td>John</td>
      <td>Smith</td>
      <td>cbs.com</td>
      <td>“.”</td>
    </tr>
    <tr>
      <td></td>
      <td>G</td>
      <td>Jones</td>
      <td>nbc.com</td>
      <td>none</td>
    </tr>
  </tbody>
Reply
Report
0
0
Jay Chow
Jay Chow
This comment was minimized by the moderator on the site 
<html>

<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<meta name=Generator content="Microsoft Word 15 (filtered)">
<style id="email_address_parser_18438_Styles">
<!--
col
	{mso-width-source:auto;}

 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Calibri",sans-serif;
	color:black;}
.MsoChpDefault
	{font-size:10.0pt;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
-->
</style>

</head>

<body lang=EN-US link="#0563C1" vlink="#954F72" style='word-wrap:break-word'>

<div class=WordSection1>

<p class=MsoNormal style='margin:0in'><span style='font-family:"Times New Roman",serif;
color:windowtext'>&nbsp;&nbsp; </span></p>

<!--The following information was generated by Microsoft Excel's Publish as Web
Page wizard.--><!--If the same item is republished from Excel, all information between the DIV
tags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!----------------------------->

<div align=center>

<table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 width=659
 style='width:494.0pt;border-collapse:collapse'>
 <col width=148 span=2 style='mso-width-source:userset;mso-width-alt:4736;
 width:111pt'><col width=99 style='mso-width-source:userset;mso-width-alt:3157;width:74pt'><col width=96 style='mso-width-source:userset;mso-width-alt:3072;width:72pt'><col width=77 style='mso-width-source:userset;mso-width-alt:2474;width:58pt'><col width=91 style='mso-width-source:userset;mso-width-alt:2901;width:68pt'>
 <tr style='height:16.0pt'>
  <td width=148 nowrap valign=bottom style='width:111.0pt;border:solid #C9C9C9 1.0pt;
  border-right:none;background:#A5A5A5;padding:.75pt .75pt 0in .75pt;
  height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><b><span style='color:white'>Subscriber
  Key</span></b></p>
  </td>
  <td width=148 nowrap valign=bottom style='width:111.0pt;border-top:solid #C9C9C9 1.0pt;
  border-left:none;border-bottom:solid #C9C9C9 1.0pt;border-right:none;
  background:#A5A5A5;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><b><span style='color:white'>Email
  Address</span></b></p>
  </td>
  <td width=99 nowrap valign=bottom style='width:74.0pt;border-top:solid #C9C9C9 1.0pt;
  border-left:none;border-bottom:solid #C9C9C9 1.0pt;border-right:none;
  background:#A5A5A5;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><b><span style='color:white'>First Name</span></b></p>
  </td>
  <td width=96 nowrap valign=bottom style='width:1.0in;border-top:solid #C9C9C9 1.0pt;
  border-left:none;border-bottom:solid #C9C9C9 1.0pt;border-right:none;
  background:#A5A5A5;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><b><span style='color:white'>Last Name</span></b></p>
  </td>
  <td width=77 nowrap valign=bottom style='width:58.0pt;border-top:solid #C9C9C9 1.0pt;
  border-left:none;border-bottom:solid #C9C9C9 1.0pt;border-right:none;
  background:#A5A5A5;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><b><span style='color:white'>Domain</span></b></p>
  </td>
  <td width=91 nowrap valign=bottom style='width:68.0pt;border:solid #C9C9C9 1.0pt;
  border-left:none;background:#A5A5A5;padding:.75pt .75pt 0in .75pt;height:
  16.0pt'>
  <p class=MsoNormal style='margin:0in'><b><span style='color:white'>Separator</span></b></p>
  </td>
 </tr>
 <tr style='height:16.0pt'>
  <td nowrap valign=bottom style='border-top:none;border-left:solid #C9C9C9 1.0pt;
  border-bottom:solid #C9C9C9 1.0pt;border-right:none;background:#EDEDED;
  padding:.75pt .75pt 0in .75pt;height:16.0pt;box-sizing: border-box'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'></span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>Jane</span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt;box-sizing: border-box'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>Doe</span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>abc.com</span></p>
  </td>
  <td nowrap valign=bottom style='border-top:none;border-left:none;border-bottom:
  solid #C9C9C9 1.0pt;border-right:solid #C9C9C9 1.0pt;background:#EDEDED;
  padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>“_”</span></p>
  </td>
 </tr>
 <tr style='height:16.0pt'>
  <td nowrap valign=bottom style='border-top:none;border-left:solid #C9C9C9 1.0pt;
  border-bottom:solid #C9C9C9 1.0pt;border-right:none;padding:.75pt .75pt 0in .75pt;
  height:16.0pt;box-sizing: border-box'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'></span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>John</span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt;box-sizing: border-box'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>Smith</span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>cbs.com</span></p>
  </td>
  <td nowrap valign=bottom style='border-top:none;border-left:none;border-bottom:
  solid #C9C9C9 1.0pt;border-right:solid #C9C9C9 1.0pt;padding:.75pt .75pt 0in .75pt;
  height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>‘,”</span></p>
  </td>
 </tr>
 <tr style='height:16.0pt'>
  <td nowrap valign=bottom style='border-top:none;border-left:solid #C9C9C9 1.0pt;
  border-bottom:solid #C9C9C9 1.0pt;border-right:none;background:#EDEDED;
  padding:.75pt .75pt 0in .75pt;height:16.0pt;box-sizing: border-box'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'></span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>G</span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>Jones</span></p>
  </td>
  <td nowrap valign=bottom style='border:none;border-bottom:solid #C9C9C9 1.0pt;
  background:#EDEDED;padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>nbc.com</span></p>
  </td>
  <td nowrap valign=bottom style='border-top:none;border-left:none;border-bottom:
  solid #C9C9C9 1.0pt;border-right:solid #C9C9C9 1.0pt;background:#EDEDED;
  padding:.75pt .75pt 0in .75pt;height:16.0pt'>
  <p class=MsoNormal style='margin:0in'><span style='color:#A6A6A6'>none</span></p>
  </td>
 </tr>
 <tr>
  <td width=148 nowrap valign=bottom style='width:111.0pt;padding:.75pt .75pt 0in .75pt'></td>
  <td width=148 nowrap valign=bottom style='width:111.0pt;padding:.75pt .75pt 0in .75pt'></td>
  <td width=99 nowrap valign=bottom style='width:74.0pt;padding:.75pt .75pt 0in .75pt'></td>
  <td width=96 nowrap valign=bottom style='width:1.0in;padding:.75pt .75pt 0in .75pt'></td>
  <td width=77 nowrap valign=bottom style='width:58.0pt;padding:.75pt .75pt 0in .75pt'></td>
  <td width=91 nowrap valign=bottom style='width:68.0pt;padding:.75pt .75pt 0in .75pt'></td>
 </tr>
</table>

</div>

<p class=MsoNormal style='margin:0in'><span style='font-family:"Times New Roman",serif;
color:windowtext'>&nbsp;</span></p>

<!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></div>

</body>

</html>
Reply
Report
0
0
Jay Chow
Jay Chow
This comment was minimized by the moderator on the site
This supports any separator by simply adding or replacing the MIN array. It also proper cases the name:

** First Name **

=PROPER(
    LEFT(
        LEFT(
            [@[Email Address]],
            FIND(
                "@",
                [@[Email Address]]
            ) - 1
        ),
        MIN(
            IFERROR(
                FIND(
                    {
                     ".",
                     "_"
                    },
                    LEFT(
                        [@[Email Address]],
                        FIND(
                            "@",
                            [@[Email Address]]
                        ) - 1
                    )
                ),
                ""
            )
        ) - 1
    )
)


** Last Name **

=PROPER(
    RIGHT(
        LEFT(
            [@[Email Address]],
            FIND(
                "@",
                [@[Email Address]]
            ) - 1
        ),
        LEN(
            LEFT(
                [@[Email Address]],
                FIND(
                    "@",
                    [@[Email Address]]
                ) - 1
            )
        ) -
        MIN(
            IFERROR(
                FIND(
                    {".","_"},
                    LEFT(
                        [@[Email Address]],
                        FIND(
                            "@",
                            [@[Email Address]]
                        ) - 1
                    )
                ),
                ""
            )
        )
    )
)
Reply
Report
0
0
Max
Max
This comment was minimized by the moderator on the site
I realized using the @ within the command also helped but did not isolate the first and last name where applicable.
=PROPER(LEFT(A4,FIND("@",A4)-1))

I will keep researching and hopefully be able to fine tune better

Thank you very much for this article.
Reply
0
0
Rated 5 out of 5
Max
Max
This comment was minimized by the moderator on the site
Thank you very much for this article. I have another question regarding these formula.
- Say I have these scenarios - I want to separate FNAME & LNAME from this email
With this command =PROPER(LEFT(A4,FIND("_",A4)-1)); I will be able to produce Ann Bee, but this sign "__ must be changed to "." ,

How do I fix this when this happens say I have - this previous command will produce abee@yahoo as the first name when I am looking to achieve Abee....please can you help.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations