Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: SUBSTITUTE

Izolo kumarathon 30 Excel isebenza ngezinsuku ezingama-30 sisebenzise umsebenzi OFFSET (OFFSET) ukubuyisela ireferensi, futhi wabona ukuthi ifana kakhulu nomsebenzi INDEX (INDEX). Ngaphezu kwalokho, sifunde ukuthi umsebenzi OFFSET (OFFSET) ibalwa kabusha noma nini lapho idatha eseshidini lokusebenzela ishintsha, futhi INDEX (INDEX) kuphela lapho ushintsha izimpikiswano zayo.

Ngosuku lwama-27 lwemarathon, sizofunda umsebenzi I-SUBSTITUTE (NGETHUNYELWE). Njengomsebenzi REPLACE (REPLACE), ifaka esikhundleni sombhalo omdala omusha, futhi ingenza nokumiselela okuningi kombhalo ofanayo kuyunithi yezinhlamvu.

Njengoba umkhuba ubonisa, kwezinye izimo kuyashesha futhi kulula ukusebenzisa imiyalo Thola/Faka esikhundleni (Thola/Faka esikhundleni) uma kudingekile ukwenza icala lokumiselela lizwele.

Ngakho-ke, ake sibhekisise ulwazi nezibonelo ezikulo I-SUBSTITUTE (NGETHUNYELWE). Uma unolunye ulwazi noma izibonelo zokuthi usetshenziswa kanjani lesi sici, sicela wabelane ngaso emazwaneni.

Umsebenzi 27: SUBSTITUTE

Umsebenzi I-SUBSTITUTE (SUBSTITUTE) esikhundleni sombhalo omdala ngombhalo omusha phakathi kweyunithi yezinhlamvu yombhalo. Umsebenzi uzomiselela zonke izimpinda zombhalo omdala kuze kube yilapho kuhlangatshezwana nombandela othile. Kuyazwela.

Ungawusebenzisa kanjani umsebenzi we-SUBSTITUTE?

Umsebenzi I-SUBSTITUTE (SUBSTITUTE) esikhundleni sombhalo omdala ngombhalo omusha phakathi kweyunithi yezinhlamvu yombhalo. Ungakwazi ukuyisebenzisela:

  • Shintsha igama lesifunda kunhlokweni yombiko.
  • Susa izinhlamvu ezingaphrinteki.
  • Shintsha uhlamvu lwesikhala sokugcina.

I-SUBSTITUTE Syntax

Umsebenzi I-SUBSTITUTE (SUBSTITUTE) ine-syntax elandelayo:

SUBSTITUTE(text,old_text,new_text,instance_num)

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

  • umbhalo (umbhalo) – iyunithi yezinhlamvu yombhalo noma isixhumanisi lapho umbhalo uzoshintshwa khona.
  • umbhalo_omdala (old_text) - umbhalo ozoshintshwa.
  • umbhalo_omusha (umbhalo_omusha) - umbhalo ozofakwa.
  • inombolo_yesibonelo (inombolo_yokungena) inombolo yokwenzeka kombhalo ozoshintshwa (kuyakhetheka).

SUBSTITUTE Izicupho

  • Umsebenzi I-SUBSTITUTE (SUBSTITUTE) ingamiselela zonke izimpinda zombhalo omdala, ngakho-ke uma udinga kuphela ukufaka esikhundleni sesenzeko esithile, sebenzisa ukuphikisana. inombolo_yesibonelo (inombolo_yokufaka).
  • Uma udinga ukwenza esikhundleni esingazweli, sebenzisa umsebenzi REPLACE (BUSELELA).

Isibonelo sokuqala: Ukushintsha igama lesifunda esihlokweni sombiko

Ukusebenzisa imisebenzi I-SUBSTITUTE (SUBSTITUTE) Ungakha isihloko sombiko esishintsha ngokuzenzakalelayo kuye ngokuthi yisiphi isifunda esikhethiwe. Kulesi sibonelo, isihloko sombiko sifakwe kuseli C11, eliqanjwe igama I-RptTitle. Uphawu yyy kumbhalo wesihloko kuzothathelwa indawo igama lesifunda esikhethwe kuseli D13.

=SUBSTITUTE(RptTitle,"yyy",D13)

=ПОДСТАВИТЬ(RptTitle;"yyy";D13)

Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: SUBSTITUTE

Isibonelo sokuqala: Susa izinhlamvu ezingaphrinteki

Lapho ukopisha idatha kuwebhusayithi, izinhlamvu zesikhala ezengeziwe zingavela embhalweni. Umbhalo ungaqukatha kokubili izikhala ezivamile (uhlamvu 32) kanye nezikhala ezinganqamuki (uhlamvu 160). Uma uzama ukususa kubo, uzothola ukuthi umsebenzi I-TRIM (TRIM) ayikwazi ukususa izikhala ezinganqamuki.

Ngenhlanhla, ungasebenzisa umsebenzi I-SUBSTITUTE (SUBSTITUTE) ukuze umiselele isikhala ngasinye esinganqamuki ngesivamile, bese usebenzisa umsebenzi I-TRIM (TRIM), susa zonke izikhala ezengeziwe.

=TRIM(SUBSTITUTE(B3,CHAR(160)," "))

=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(B3;СИМВОЛ(160);" "))

Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: SUBSTITUTE

Isibonelo sesi-3: Ukushintsha umlingisi wesikhala wokugcina

Ukuze ugweme ukufaka esikhundleni sakho konke ukuvela kweyunithi yezinhlamvu yombhalo, ungasebenzisa i-agumenti inombolo_yesibonelo (inombolo_yokufaka) ukukhombisa ukuthi isiphi isenzakalo esingashintshwa. Isibonelo esilandelayo siwuhlu lwesithako seresiphi lapho uhlamvu lokugcina lwesikhala kuphela oludinga ukushintshwa.

Umsebenzi LEN (DLSTR) kuseli C3 ibala inani lezinhlamvu kuseli B3. Umsebenzi I-SUBSTITUTE (SUBSTITUTE) imiselela zonke izinhlamvu zesikhala esimhlophe ngochungechunge olungenalutho, nomsebenzi wesibili LEN (i-DLSTR) ithola ubude beyunithi yezinhlamvu ecutshunguliwe. Ubude buyizinhlamvu ezi-2 mfishane, okusho ukuthi bekunezikhala ezi-2 ochungechungeni.

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

=ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";""))

Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: SUBSTITUTE

Kuseli D3, umsebenzi I-SUBSTITUTE (SUBSTITUTE) esikhundleni sohlamvu lwesibili lwesikhala ngochungechunge olusha » | “.

=SUBSTITUTE(B3," "," | ",C3)

=ПОДСТАВИТЬ(B3;" ";" | ";C3)

Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: SUBSTITUTE

Ukuze ungasebenzisi amafomula amabili ukuxazulula le nkinga, ungawahlanganisa abe yiwodwa ende:

=SUBSTITUTE(B3," "," | ",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))

=ПОДСТАВИТЬ(B3;" ";" | ";ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";"")))

shiya impendulo