Skip to main content

Conas an chéad fhocal / an focal deireanach / an naoú focal a bhaint as sreangán téacs in Excel?

Ar fhulaing tú riamh leis an bhfadhb gur gá duit focal áirithe a bhaint as an sreangán téacs i mbileog oibre? Mar shampla, tá an raon teaghráin téacs seo a leanas agat a theastaíonn chun an chéad / an focal deireanach nó an naoú focal a fháil uathu, anseo is féidir liom labhairt faoi roinnt bealaí éifeachtacha chun tú a réiteach.


Sliocht an chéad fhocal nó an t-ainm deireanach as sreangán téacs le Foirmlí

Más gá duit an chéad fhocal a bhaint as liosta teaghráin téacs, is féidir leis na foirmlí seo a leanas cabhrú leat.

Chun an chéad fhocal a bhaint, cuir an fhoirmle thíos isteach i gcill bhán, agus ansin tarraing láimhseáil AutoFill na cille seo chuig an raon de réir mar is gá duit.

= IF (ISERR (FIND ("", A2)), "", LEFT (A2, FIND ("", A2) -1))

Chun an focal deireanach a bhaint as gach cill, cuir an fhoirmle seo i bhfeidhm:

= IF (ISERR (FIND ("", A2)), "", CEART (A2, LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN ( SUBSTITUTE (A2, "", "")))))))

Agus anois feicfidh tú go mbaintear an chéad fhocal nó an focal deireanach as gach cill.

Nótaí: Sna foirmlí thuas, A2 léiríonn sé an chill as a mbainfidh tú an chéad fhocal nó an focal deireanach.

Deacair foirmlí fada casta a chur de ghlanmheabhair? Uirlis iontach a chuidíonn leat an naoú Word w a bhaintith cúpla cad a tharlaíonn ach!

Os cionn foirmlí fada ní féidir ach an chéad fhocal agus an focal deireanach a bhaint astu, ach beidh siad neamhúsáidte chun an naoú focal sonraithe a bhaint, a deir an dara focal, an séú focal, srl. Fiú amháin is féidir leat foirmlí a dhéanamh amach chun an fhadhb a réiteach, caithfidh na foirmlí a bheith ró-chasta le cur de ghlanmheabhair agus le cur i bhfeidhm. Anseo, moltar Sliocht an naoú focal i ngné cille de Kutools for Excel, agus cuideoidh sé leat an naoú focal a bhaint chomh furasta agus is féidir!


Kutools le haghaidh Excel - Supercharge Excel le níos mó ná 300 uirlisí riachtanacha. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Get sé anois

Sliocht an naoú focal as sreangán téacs le Feidhm Sainithe ag an Úsáideoir

Más mian leat an dara, an tríú focal nó an naoú focal ar bith a bhaint as an sreangán téacs, is féidir leat feidhm atá sainithe ag an úsáideoir a chruthú chun déileáil leis.

1. Coinnigh síos an ALT + F11 eochracha a oscailt Fuinneog Microsoft Visual Basic for Applications.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil fhuinneog.

Function FindWord(Source As String, Position As Integer)
'Update 20131202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. Ansin sábháil an cód, sa sampla seo, gheobhaidh mé an tríú focal ón tsreang, mar sin clóscríobh an fhoirmle seo = aimsfhocal (A2,3) isteach i gcill bhán B2, agus ansin tarraing láimhseáil AutoFill na cille seo chuig an raon de réir mar is gá duit. Féach an pictiúr:

Nóta: San fhoirmle thuas, A2 an cill inar mian leat focal a bhaint as, an uimhir 3 léiríonn sé an tríú focal sa téad a bhainfidh tú as, agus is féidir leat iad a athrú de réir mar is gá duit.


Sliocht gach focal as sreangán téacs agus déan iad a liostáil go cothrománach nó go hingearach

Tabharfaidh an modh seo Kutools isteach le haghaidh Excel Cealla Scoilt fóntais chun gach focal a bhaint as cealla teaghrán téacs, agus ansin focail eastósctha a liostáil go cothrománach nó go hingearach bunaithe ar do riachtanas.

Kutools le haghaidh Excel - Pacáilte le breis agus 300 uirlis riachtanach le haghaidh Excel. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Íoslódáil anois!

1. Roghnaigh na cealla teaghrán téacs a mbainfidh tú a gcuid focal astu, agus cliceáil ar an Kutools > Cumaisc & Scoilt > Cealla Scoilt.

2. Sa bhosca dialóige oscailt Cealla Scoilt, sonraigh an cineál scoilte sa cineál roinn, seiceáil an Spás rogha sa Sonraigh deighilteoir alt, agus cliceáil ar an Ok cnaipe. Féach an pictiúr:

3. Anois sonraigh an raon ceann scríbe a ndéanfaidh tú focail eastósctha a aschur ann, agus cliceáil ar an OK cnaipe

Má sheiceáil tú an Scoilt go Colúin rogha sa dialóg Cealla Scoilt thuas, baintear gach focal as gach sreang téacs agus liostáiltear go hingearach é.

Má sheiceáil tú an Scoilt go Sraitheanna rogha sa dialóg Cealla Scoilt thuas, baintear gach focal as gach sreang téacs agus liostáiltear go cothrománach é.


Sliocht an naoú focal as sreangán téacs in Excel le huirlis iontach

Má tá Kutools suiteáilte agat le haghaidh Excel, is féidir leat a Helper Foirmle> Sliocht an naoú focal sa ghné cille a úsáid chun an naoú focal a bhaint go tapa ón gcill shonraithe.

Kutools le haghaidh Excel - Pacáilte le breis agus 300 uirlis riachtanach le haghaidh Excel. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Íoslódáil anois!

1. Roghnaigh an chill a gcuirfidh tú an focal eastósctha air, agus cliceáil Kutools > Cúntóir Foirmle > Cúntóir Foirmle chun an ghné seo a chumasú.

2. Sa dialóg Helper Formulas, déan mar a leanas:
(1) Roghnaigh Téacs ó na Cineál foirmle liosta anuas;
(2) Cliceáil chun aird a tharraingt Sliocht an naoú focal i gcill sa Roghnaigh foirmle bosca liosta;
(3) Sa Cell bosca, sonraigh an chill as a mbainfidh tú focal;
(4) In An Nú bosca, sonraigh an uimhir.

3. Cliceáil ar an Ok cnaipe. Más gá, tarraing Láimhseáil AutoFill na cille foirmle le do thoil agus cóipeáil an fhoirmle chuig cealla eile.


Taispeántas: bain gach focal as sreangán téacs agus déan iad a liostáil go cothrománach nó go hingearach


Kutools le haghaidh Excel: Breis is 300 uirlis láimhe ar do mhéar! Cuir tús le do thriail saor in aisce 30-lá gan aon srianta gné inniu. Íosluchtaigh Anois!

Earraí gaolmhara:

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I've created the FindWord custom function as described and it worked perfectly but it stopped working the next day. I looked in the VBA window and the Module is still there. Any idea why the function is not working anymore?
This comment was minimized by the moderator on the site
Dark Chocolate 25gm box 12 pcs

Dark Chocolate 20gm*24 box

White Chocolate 15gm

White Chocolate 25gm*24

Biscuits W/Marshmallow300gm

Chocolate 40gm

Can some plz help to extract the numbers before "gm", for example : 25,20,15,25,300,40
This comment was minimized by the moderator on the site
Hi I would like to know how to use the VBA model but I have sentences of only one word, and i would like to pick the first word even if there is only one word.

Thank you
This comment was minimized by the moderator on the site
Hi Maura,
The VBA cannot extract the first word if there is only word in the cell. However, it’s recommended to apply the Text to Column to extract the first word of every cell in a column quickly.
1. Select the column, copy, and paste in a blank column.
2. Keep the new column selected, click Data > Text to Column.
3. In the Text column dialog, select Delimited, and then check Space as delimiters, and finally click Finish.
4. Now all words are separated by space. For the extracted words, you can remove all columns except the first one.
This comment was minimized by the moderator on the site
i want to remove the last word in cell and data is like this "/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Cdtr/CtctDtls/FaxNb" can i have formula
This comment was minimized by the moderator on the site
Thanks so much for these! I have a question, though: how do I extract a number or group of numbers from a cell or textbox? For instance, if I have [37.5" x 21'] in cell A1, how could extract 37.5 into A2? There could be any number of characters and numbers in A1, but I'll always want the first number to the left of the "X", and the first number to the right. Not sure if you've covered something like this elsewhere, so I thought I'd ask here. (Not sure if this went through the first time, so here it is again.)
This comment was minimized by the moderator on the site
Thank you! It works fine called in a Sub.
This comment was minimized by the moderator on the site
the original formula isn't working for me, I keep getting #NAME?, anyone able to help? I am using Excel 2013 and this VBA would save me sooooo much time
This comment was minimized by the moderator on the site
The original VBA code posted here would save me so much time, however whenever I type in the =findword formula, I then get #NAME?, anyone able to help me? I am using Excel 2013
This comment was minimized by the moderator on the site
Hi, I am having cell with values like SAN_UN_TC1,SAN_UN_TC2,PEP_HR_TC1 I would like to extract first words liek SAN /PEP into one cell and words liek UN/HR to another cell How could i do it, Any help is appreciated. Thanks, Shiva
This comment was minimized by the moderator on the site
If number of characters in string (SAN_UN_TC1) is fixed (3_2_3), it's simple: column 1: =LEFT(B15,3) column 2: =MID(B15,5,2) column 3: =RIGHT(B15,3)
This comment was minimized by the moderator on the site
Many thanks for sharing VBA code....saved me a lot of time
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations