Skip to main content

Conas an chéad litir de gach focal a bhaint as cill?

Samhlaigh go bhfuil liosta ainmneacha tíortha ar do bhileog oibre agus gur mhaith leat an chéad litir de gach focal sna hainmneacha seo a bhaint as. Ní sholáthraíonn Excel gné dhíreach chun litreacha tosaigh gach focal i gcill a bhaint. Mar sin féin, tabharfaidh mé isteach roinnt modhanna praiticiúla chun an tasc seo a chur i gcrích go héifeachtach, rud a fhágann go mbeidh an próiseas simplí agus éifeachtach.

Sliocht an chéad litir de gach focal as Cill leis an bhfoirmle

Chun an chéad litir de gach focal a fháil laistigh de chill, is féidir leis an bhfoirmle eagair seo a leanas cabhrú leat.

1. Cóipeáil an fhoirmle seo a leanas isteach i gcill bhán áit ar mhaith leat an toradh a fháil, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad toradh a fháil.

=CONCAT(LEFT(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"),1))

2. Ansin, tarraing an fhoirmle síos chun na cealla eile a líonadh. Bainfidh an gníomh seo amach go huathoibríoch an chéad litir de gach focal i ngach cill ag an am céanna. Féach ar an scáileán:

Míniú ar an bhfoirmle seo:
  • SUBSTITUTE(A2," "," "):Cuireann an fheidhm seo . Scarann ​​sé seo go héifeachtach gach focal sa téacs leis na clibeanna XML seo. Mar shampla, má tá "Dia duit an Domhain" in A2, is "Dia duit an Domhain" é sa chuid seo den fhoirmle.
  • " "&...&" ": wraps an chuid seo mar thoradh ar an bhfeidhm SUBSTITUTE leis ag an tús agus ag an deireadh. Ag leanúint leis an sampla, déantar Hello World den teaghrán anois, rud a chruthaíonn struchtúr XML bailí ina bhfuil gach focal faoi iamh laistigh de chlibeanna.
  • FILTERXML(...,"//b"): Úsáidtear FILTERXML chun an teaghrán XML a cruthaíodh sna céimeanna roimhe seo a pharsáil. Roghnaíonn an cheist XPath //b gach eilimint laistigh de na clibeanna, ie, gach focal sa bhunteaghrán. Ag baint úsáide as ár sampla, thabharfadh FILTERXML eagar le dhá eilimint ar ais: "Dia duit" agus "Domhanda".
  • LEFT(..., 1): Cuirtear an fheidhm LEFT i bhfeidhm ansin ar gach eilimint den eagar a chuirtear ar ais le FILTERXML, ag baint amach an chéad litir de gach focal. Sa sampla, bheadh ​​​​"H" agus "W" mar thoradh air seo.
  • CONCAT(...): Ar deireadh, comhdhlúthaíonn an fheidhm CONCAT gach eilimint den eagar ina sreang amháin. Le haghaidh ár sampla "Dia duit Domhanda", bheadh ​​​​sé comhchuí "H" agus "W" a thabhairt ar aird "HW".

Sliocht an chéad litir de gach focal ó Cill le Kutools AI Aide

Sliocht go tapa an chéad litir de gach focal i gcill le Kutools AI Aide. Níl gá le foirmlí casta; déanann cúntóir AI an tasc duit a uathoibriú, rud a fhágann go bhfuil próiseáil sonraí simplí agus éifeachtach. Sruthlaigh do shreabhadh oibre Excel agus déan do chuid oibre níos éasca. Bain triail as Kutools AI Aide agus taithí a fháil ar oibríochtaí cliste Excel!

nótaí: Chun seo a úsáid Kutools AI Aide of Kutools le haghaidh Excel, Le do thoil Íoslódáil agus a shuiteáil Kutools do Excel an chéad.

Tar éis Kutools a shuiteáil le haghaidh Excel, cliceáil le do thoil Kutools AI > AI Aide a oscailt Kutools AI Aide pána:

  1. Roghnaigh an liosta sonraí, ansin clóscríobh do riachtanas isteach sa bhosca comhrá, agus cliceáil Seol cnaipe nó brúigh Iontráil eochair chun an cheist a sheoladh;
  2. Tar éis anailís a dhéanamh, cliceáil Rith cnaipe a rith. Déanfaidh Kutools AI Aide d'iarratas a phróiseáil ag baint úsáide as AI agus cuir na torthaí ar ais go díreach in Excel.

Sliocht an chéad litir de gach focal as Cill le Feidhm Shainithe ag an Úsáideoir

Is tasc é an chéad litir de gach focal a bhaint as cill ar féidir é a uasmhéadú go mór trí Fheidhm Sainmhínithe Úsáideora (UDF) a úsáid in Excel. Scrúdaíonn an chuid seo conas UDF a chruthú agus a úsáid chun an tasc seo a dhéanamh go héifeachtach.

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 Fuinneog an Mhodúil.

Function GetFirstLetters(rng As Range) As String
'Updateby Extendoffice
    Dim arr
    Dim I As Long
    arr = VBA.Split(rng, " ")
    If IsArray(arr) Then
        For I = LBound(arr) To UBound(arr)
            GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
        Next I
    Else
        GetFirstLetters = Left(arr, 1)
    End If
End Function

3. Ansin sábháil agus dún an cód seo, téigh ar ais ar an mbileog oibre, agus cuir isteach an fhoirmle seo = GetFirstLetters (A2) isteach i gcill bhán. Agus ansin, tarraing an láimhseáil líonta chuig na cealla ar mhaith leat an fhoirmle seo a chur i bhfeidhm. Agus na chéad litreacha go léir bainte as an tsraith focal, féach screenshot:

Earraí gaolmhara:

  • Sliocht an chéad nó an dá fhocal deiridh nó n focal as teaghrán téacs
  • Má tá liosta de théada téacs agat atá scartha de réir spáis, agus anois, ba mhaith leat an chéad nó an trí fhocal nó an focal deireanach a bhaint as luach na cille chun an toradh scáileáin seo a leanas a fháil. An t-alt seo, tabharfaidh mé isteach roinnt foirmlí chun an chéad nó an dara focal nó an focal deireanach a bhaint as sreangán téacs in Excel.
  • Sliocht téacs roimh/tar éis spás nó camóg amháin
  • Nuair is mian leat an téacs a bhaint roimh nó tar éis an spáis ón liosta mar a thaispeántar thíos, an bhfuil bealach maith agat chun é a chur i gcrích? Lig dom roinnt bealaí casta a insint duit chun téacs a bhaint roimh spás nó ina dhiaidh sin in Excel amháin.
  • Sliocht seoladh ríomhphoist as teaghrán téacs
  • Nuair a iompórtálann tú roinnt seoltaí ríomhphoist ón Suíomh Gréasáin chuig bileog oibre Excel, bíonn téacs nach mbaineann le hábhar i gcónaí, ach anois níl uait ach na seoltaí ríomhphoist íon a bhaint as an sreangán téacs (féach na scáileáin scáileáin seo a leanas). Conas a d’fhéadfá na seoltaí ríomhphoist a fháil go tapa ón téacs cealla?
  • Sliocht teaghrán idir dhá charachtar éagsúla
  • Má tá liosta de shreangáin agat in Excel a chaithfidh tú cuid de shreang a bhaint idir dhá charachtar as an scáileán thíos a thaispeántar, conas é a láimhseáil chomh tapa agus is féidir? Anseo, tugaim isteach roinnt modhanna chun an post seo a réiteach.

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
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 ...

Tuairisc

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 (18)
No ratings yet. Be the first to rate!
Sharmane
Sharmane
This comment was minimized by the moderator on the site
Hello, If the the nth Word is enclosed in a (), it is returning the ( since it comes before the First Letter.

Example:
Word Word (Word)

How to return WWW instead of WW( ?
Reply
Report
0
0
skyyang
skyyang     Sharmane
This comment was minimized by the moderator on the site
Hello, Sharmane
Maybe the following code can help you:
Function GetFirstLetters(rng As Range) As String
    Dim arr() As String
    Dim i As Long
    
    arr = VBA.Split(rng.Value, " ")
    
    For i = LBound(arr) To UBound(arr)
        If Left(arr(i), 1) <> "(" And Right(arr(i), 1) <> ")" Then
            GetFirstLetters = GetFirstLetters & Left(arr(i), 1)
        ElseIf Left(arr(i), 1) = "(" And Right(arr(i), 1) = ")" Then
            GetFirstLetters = GetFirstLetters & Mid(arr(i), 2, 1)
        End If
    Next i
End Function


Please have a try, thank you!
Reply
Report
0
0
samual
samual
This comment was minimized by the moderator on the site
Another suggestion if using Microsoft 365: =TEXTJOIN("",,LEFT(TEXTSPLIT(D9," "),1)) where the source string is in B9.
Wrap in UPPER function to enforce uppercase: =UPPER(TEXTJOIN("",,LEFT(TEXTSPLIT(D9," "),1)))
@Pradeep Gyawali -> add space: =UPPER(TEXTJOIN(" ",,LEFT(TEXTSPLIT(D9," "),1)))
Reply
0
0
SARAH
SARAH
This comment was minimized by the moderator on the site
Wondering how to add this into the existing formula I have that works to bring the value over from a cell in another sheet? I only want to bring over the first character in each cell. Here's the formula

=IF(LOOKUP(2,1/(OriginalSubmission!D:D<>""),ROW(OriginalSubmission!D:D))=ROW(OriginalSubmission!D4),"",INDIRECT("OriginalSubmission!D5:"&"D"&(LOOKUP(2,1/(OriginalSubmission!D:D<>""),ROW(OriginalSubmission!D:D)))))
Reply
0
0
skyyang
skyyang     SARAH
This comment was minimized by the moderator on the site
Hello, SARAH
Do you mean extracting each first character in cells from another worksheet? If so, you just need to copy and paste the code in this article, and then apply this formula into another sheet.
=GetFirstLetters(OriginalSubmission!D4)


Note: OriginalSubmission is the sheet name that you want to extract charatcers from.
Please try, hope it can help you!
Reply
Report
0
0
Pradeep Gyawali
Pradeep Gyawali
This comment was minimized by the moderator on the site
How to create space between the letters?

South Korea= S K
Reply
0
0
skyyang
skyyang     Pradeep Gyawali
This comment was minimized by the moderator on the site
Hi, Gyawali
If you want to add space for each character, please apply the following VBA code:
Function GetFirstLetters(Rng As Range) As String
'Updateby Extendoffice
    Dim xStr
    Dim arr
    Dim I As Long
    xStr = " "
    arr = VBA.Split(Rng, " ")
    If IsArray(arr) Then
        For I = LBound(arr) To UBound(arr)
            GetFirstLetters = GetFirstLetters & Left(arr(I), 1) & xStr
        Next I
    Else
        GetFirstLetters = Left(arr, 1) & xStr
    End If
End Function


After insert the code, and then apply this formula: =GetFirstLetters(A2) to get the result you need.
Please try, hope it can help you!
Reply
Report
0
0
holly
holly
This comment was minimized by the moderator on the site
Why is this giving me the first 2 letters in each word?
Reply
0
0
Griggs
Griggs
This comment was minimized by the moderator on the site
This is awesome. Such a time saver
Reply
0
0
Steven
Steven
This comment was minimized by the moderator on the site
This code is good for upto five words, where D20 is the cell with data.



=IF(ISERR(LEFT($D$20,1)&MID($D$20,SEARCH(" ",$D$20)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",
$D$20)+1)+1)+1)+1,1)),IF(ISERR(LEFT($D$20,1)&MID($D$20,SEARCH(" ",$D$20)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1)+1,1)),
IF(ISERR(LEFT($D$20,1)&MID($D$20,SEARCH(" ",$D$20)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1,1)),
IF(ISERR(LEFT($D$20,1)&MID($D$20,SEARCH(" ",$D$20)+1,1)),
IF(ISERR(LEFT($D$20,1)),"",LEFT($D$20,1)),LEFT($D$20,1)
&MID($D$20,SEARCH(" ",$D$20)+1,1)),LEFT($D$20,1)&MID($D$20,SEARCH(" ",$D$20)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1,1)),
LEFT($D$20,1)&MID($D$20,SEARCH(" ",$D$20)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1)+1,1)),LEFT($D$20,1)
&MID($D$20,SEARCH(" ",$D$20)+1,1)&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)+1)+1,1)
&MID($D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20,SEARCH(" ",$D$20)+1)
+1)+1)+1,1))
Reply
0
0
Oleg
Oleg
This comment was minimized by the moderator on the site
Подскажите пожалуйста, можно ли модифицировать код чтобы забиралась не первые а Заглавные буквы?
Reply
0
0
nawal jha
nawal jha
This comment was minimized by the moderator on the site
i think it has one bug, it's automatically removed from module when sheet is closed, need to again every time when open sheet same process to be required, please advice how to save this formula in excel permanently.
Reply
0
0
Harib Oliyil
Harib Oliyil     nawal jha
This comment was minimized by the moderator on the site
Pls save excel as Excel Macro-Enablel work book.
Reply
0
0
NoOne
NoOne
This comment was minimized by the moderator on the site
Please include this part: If you create a function called DISCOUNT in a workbook called Personal.xlsb and you call that function from another workbook, you must type =personal.xlsb!discount(), not simply =discount(). https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations