Tip: Lingwi oħra huma tradotti minn Google. Tista 'żżur il - English verżjoni ta 'din ir-rabta.
log in
x
or
x
x
reġistru
x

or

Kif tiġbed l-ewwel ittra ta 'kull kelma miċ-ċellula?

Ħalli jkollok lista tal-isem tal-pajjiżi fil-folja ta 'xogħol tiegħek, u issa, għandek bżonn li tiġbed l-ewwel ittra ta' kull kelma fiċ-ċellola kif ġejja screenshots murija. Normalment, m'hemm l-ebda karatteristika diretta li tiġbor l-ewwel ittra ta 'kull kelma f'Excel. Iżda, hawnhekk, nista 'nitkellem dwar mod utli biex issolvi din il-ħidma.

doc-extract-first-letter1

Oħroġ l-ewwel ittra ta 'kull kelma minn Ċellula b'Funzjoni Definita mill-Utent


vleġġa bużżieqa tal-lemin bluOħroġ l-ewwel ittra ta 'kull kelma minn Ċellula b'Funzjoni Definita mill-Utent


Fl-Excel, tista 'toħloq Funzjoni Definita mill-Utent biex jiġu estratti l-ewwel ittri ta 'kull kelma f'ċellula. Jekk jogħġbok ħu hekk:

1. Żomm il-buttuna ALT + F11 ċwievet biex tiftaħ il - Microsoft Visual Basic tieqa għall-Applikazzjonijiet.

2. Ikklikkja Daħħal > Moduli, u paste tal - kodiċi li ġej fil - Tieqa tal-Modulu. </ p>

Function GetFirstLetters(rng As Range) As String
'Update 20140325
    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. Imbagħad issalva u tagħlaq dan il-kodiċi, ibgħat lura l-worksheet, u daħħal din il-formula = GetFirstLetters (A2) (A2 tindika ċ-ċellula li tixtieq tiġbed l-ewwel ittra, tista 'tbiddilha kif trid) f'ċellula vojta. Ara l-screenshot:

doc-extract-first-letter1

4. U mbagħad ippressa Ikteb ċavetta, u tagħżel iċ-ċellula B2, imbagħad iġbed il-manku tal-mili liċ-ċelloli li tixtieq tapplika din il-formula. U l-ewwel ittri kollha ġew estratti mis-serje ta 'kliem, ara screenshot:

doc-extract-first-letter1


Oġġett relatat:

Kif tiġbed l-ewwel / l-aħħar / nth kelma mis-sekwenza tat-test fl-Excel?



Għodda tal-Produttività Rakkomandata

Uffiċċju Tab

star tad-deheb 1 Ġib tabs handy għal Excel u softwer ieħor tal-Uffiċċju, bħal Chrome, Firefox u Internet Explorer ġdid.

Kutools għal Excel

star tad-deheb 1 Amazing! Żid il-produttività tiegħek f'minuti 5. M'għandekx bżonn xi ħiliet speċjali, ħlief sagħtejn kuljum!

star tad-deheb 1 300 Karatteristiċi Ġodda għall-Excel, Għamla Excel Ħafna Faċli u Qawwija:

  • Merge Cell / Ringieli / Kolonni mingħajr ma titlef id-Data.
  • Għaqqad u Tikkonsolida Folji Multipli u Kotba tax-Xogħol.
  • Qabbel ir-Ranges, Kopja Multipla Ranges, Ikkonverti Test sa Data, Unità u l-Konverżjoni Munita.
  • Għadd bil-Kuluri, Subtotali tal-Pejġing, Sort Avvanzat u Iffiltra Super,
  • Iktar Agħżel / Daħħal / Ħassar / Test / Format / Rabta / Kumment / Kotba tax-Xogħol / Worksheets Għodda ...

Screenshot ta &#39;Kutools għal Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Oleg · 10 months ago
    Подскажите пожалуйста, можно ли модифицировать код чтобы забиралась не первые а Заглавные буквы?
  • To post as a guest, your comment is unpublished.
    nawal jha · 2 years ago
    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.
  • To post as a guest, your comment is unpublished.
    NoOne · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Adam Pavey · 2 years ago
    Very helpful. Exactly what I was after.
  • To post as a guest, your comment is unpublished.
    Sridhar · 3 years ago
    Thank you very much.. its working.. u rocks