Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: REPLACE

Izolo kumarathon 30 Excel isebenza ngezinsuku ezingama-30 sisebenzise umsebenzi INDEX (INDEX) ukuze kubuyiselwe inani leseli elitholakala empambanweni yomugqa nekholomu enikeziwe.

Ngosuku lwe-25 lwemarathon, sizonikela ngesifundo somsebenzi REPLACE (REPLACE), okuyingxenye yombhalo. Imiselela inombolo ethile yezinhlamvu kuyunithi yezinhlamvu yombhalo nomunye umbhalo.

Ngakho-ke, ake sibhekisise ulwazi nezibonelo ngomsebenzi REPLACE (REPLACE), futhi uma unolwazi olwengeziwe noma izibonelo, sicela wabelane ngazo kumazwana.

Umsebenzi 25: SHINTSHA

Umsebenzi REPLACE (REPLACE) ithatha indawo yezinhlamvu ezingaphakathi kombhalo ngokusekelwe enanini elishiwo lezinhlamvu nendawo yokuqala.

Umsebenzi we-REPLACE ungasetshenziswa kanjani?

Umsebenzi REPLACE (REPLACE) ingashintsha izinhlamvu kuyunithi yezinhlamvu yombhalo, isibonelo:

  • Shintsha ikhodi yendawo enombolweni yocingo.
  • Shintsha isikhala ngekholoni ngesikhala.
  • Sebenzisa umsebenzi owenziwe isidleke REPLACE (REPLACE) ukuze ufake amakhonco amaningi.

buyisela i-syntax

Umsebenzi REPLACE (REPLACE) ine-syntax elandelayo:

REPLACE(old_text,start_num,num_chars,new_text)

ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)

  • umbhalo_omdala (old_text) – umucu wombhalo lapho izinhlamvu zizoshintshwa khona.
  • inombolo_yokuqala (start_pos) – indawo yokuqala yezinhlamvu ezindala.
  • inombolo_yezinhlamvu (num_chars) – inombolo yezinhlamvu ezindala.
  • umbhalo_omusha (umbhalo_omusha) - umbhalo ozofakwa esikhundleni sezinhlamvu ezindala.

Ama-Traps REPLACE (REPLACE)

Umsebenzi REPLACE (REPLACE) ingena esikhundleni senombolo eshiwo yezinhlamvu eziqala endaweni eshiwo. Ukushintsha umugqa othile wombhalo noma yikuphi embhalweni wokuqala, ungasebenzisa umsebenzi I-SUBSTITUTE (SUBSTITUTE), esizoyibheka kamuva emjahweni wethu.

Isibonelo sokuqala: Ukushintsha ikhodi yendawo enombolweni yocingo

Ukusebenzisa imisebenzi REPLACE (REPLACE) Ungashintsha izinombolo zokuqala ezintathu zenombolo yocingo, isibonelo, uma ikhodi yendawo entsha isethiwe. Esimweni sethu, ikhodi yendawo entsha ifakwa kukholamu C, futhi izinombolo zocingo ezilungisiwe zivezwa kukholamu D.

=REPLACE(B3,1,3,C3)

=ЗАМЕНИТЬ(B3;1;3;C3)

Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: REPLACE

Isibonelo sesi-2: Faka isikhala esikhundleni ngekholoni ngesikhala

Ukuchaza indawo yokuqala yomsebenzi REPLACE (REPLACE), Ungasebenzisa umsebenzi THOLA (THOLA) ukuze uthole umugqa othile wombhalo noma uhlamvu. Kulesi sibonelo, sifuna ukufaka esikhundleni sesikhala sokuqala esenzeka kuyunithi yezinhlamvu yombhalo ngekholoni elandelwa yisikhala.

=REPLACE(B3,FIND(" ",B3,1),1,": ")

=ЗАМЕНИТЬ(B3;НАЙТИ(" ";B3;1);1;": ")

Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: REPLACE

Isibonelo sesi-3: Ifakwe ku-REPLACE imisebenzi yokufaka amakhonco amaningi

Umsebenzi REPLACE (REPLACE) kuvunyelwe ukubekela eyodwa phakathi kwesinye, ukuze kufakwe esikhundleni esiningi kumbhalo womthombo. Kulesi sibonelo, izinombolo zocingo kufanele zibe namakhonco ngemva kwezinhlamvu ezintathu zokuqala nangemuva kwesibili. Ukusebenzisa Zero, njengenombolo yezinhlamvu ezizofakwa esikhundleni, sizothola umphumela wokuthi azikho izinhlamvu zenombolo yocingo ezizosuswa, kuzongezwa amakhonco ama-2 kuphela.

=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;"-");8;0;"-")

Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: REPLACE

shiya impendulo