Yadda ake share haruffan farko a cikin tantanin halitta na Excel

Sau da yawa, masu amfani da editan maƙunsar bayanai na Excel suna fuskantar irin wannan aiki kamar share haruffan farko a cikin tantanin halitta. Kuna iya aiwatar da wannan hanya ta amfani da hanyoyi daban-daban, misali, ta amfani da na'urori masu haɗaka na musamman. A cikin labarin, za mu yi la'akari dalla-dalla, ta amfani da misalai, hanyoyi da yawa waɗanda ke aiwatar da kawar da haruffa a cikin tantanin halitta na bayanan tabular.

Share haruffan farko a cikin maƙunsar bayanai na Excel

Don aiwatar da wannan hanya mai sauƙi, ana amfani da aikin haɗin gwiwa na musamman. Cikakken umarnin don cire haruffan farko yayi kama da haka:

1. Misali, muna da irin wannan faranti mai ƙunshe da takamaiman saitin bayanai akan filin aiki na daftarin aiki. Muna buƙatar aiwatar da kawar da halin farko.

2. Da farko, muna buƙatar gano jimillar adadin haruffa a duk sel. Don yin wannan aikin, dole ne ka yi amfani da afaretan DLSTR. Wannan aikin yana ba ku damar ƙidaya adadin haruffa. Matsar da siginan kwamfuta zuwa cell B2 kuma zaɓi shi tare da maɓallin linzamin kwamfuta na hagu. Anan muna tuƙi a cikin tsari mai zuwa: = DLSTR(A2). Yanzu muna buƙatar kwafin wannan dabarar zuwa ƙwayoyin ƙasa. Matsar da alamar linzamin kwamfuta zuwa kusurwar dama ta ƙasa na filin B2. Siginan kwamfuta ya ɗauki siffar ƙaramar ƙara alamar inuwa mai duhu. Riƙe LMB kuma ja dabarar zuwa sauran sel.

3. A mataki na gaba, muna ci gaba da cire hali na 1 a hagu. Don aiwatar da wannan hanya, ana amfani da ma'aikaci mai suna RIGHT. Matsar da siginan kwamfuta zuwa cell B2 kuma zaɓi shi tare da maɓallin linzamin kwamfuta na hagu. Anan muna tuƙi a cikin tsari mai zuwa: =PRAWSIMV(A2;DLSTR(A2)-1). A cikin wannan dabarar, A2 shine daidaitawar tantanin halitta inda muke cire sifa ta farko daga hagu, kuma LT (A2) -1 shine adadin haruffan da aka dawo daga ƙarshen layin a gefen dama.

Ana ƙididdige wannan adadi na kowane filin ta hanyar cire haruffa ɗaya daga jimlar adadin haruffa.

4. Yanzu muna buƙatar kwafin wannan dabarar zuwa ƙwayoyin ƙasa. Matsar da alamar linzamin kwamfuta zuwa kusurwar dama ta ƙasa na filin B2. Siginan kwamfuta ya ɗauki siffar ƙaramar ƙara alamar inuwa mai duhu. Riƙe LMB kuma ja dabarar zuwa sauran sel. A sakamakon haka, mun aiwatar da kawar da halin farko a gefen hagu na kowane tantanin halitta da aka zaɓa. Shirya!

Bugu da ƙari, zaku iya amfani da ma'aikaci na musamman mai suna PSTR. Misali, muna da bayanai a cikin sel wanda aka nuna jerin adadin ma'aikata. Muna buƙatar cire haruffan farko kafin ɗigo ko sarari. Tsarin tsari zai yi kama da haka: =MID(A:A; SEARCH(".";A:A)+2;DLSTR(A:A)-NENE(".";A:A)).

Cire harafi kafin hali a cikin editan maƙunsar rubutu

Akwai yanayi lokacin da ya zama dole don share haruffa har zuwa wani takamaiman hali a cikin takaddar maƙunsar rubutu. A wannan yanayin, dabara mai sauƙi mai zuwa ta shafi: = MUSA (A1, SEARCH ("hali", A1),). Sakamakon sauyi:

• A1 shine filin da ake dubawa.
• Hali abu ne ko bayanin rubutu wanda za'a gyara tantanin halitta zuwa hagu.

Bugu da ƙari, ana iya haɗa wannan hanya tare da tsaftace bayanan "Bayan".

Share haruffa kafin waƙafi a cikin editan maƙunsar rubutu

Akwai yanayi lokacin da ya zama dole don cire wurare na ƙima a cikin daftarin aiki. A wannan yanayin, dabara mai sauƙi mai zuwa tana aiki: = MATSAYI (A1; 1; SEARCH ("&"; A1);). Sakamakon sauyi:

Cire haruffa har zuwa sarari a cikin editan falle

Akwai yanayi lokacin da ya zama dole don share haruffa har zuwa sarari a cikin daftarin aiki. A wannan yanayin, dabara mai sauƙi mai zuwa ta shafi: = MUSA (A1;1; BINCIKE("&";A1);). Sakamakon sauyi:

Ana cirewa tare da mai sarrafa SUBSTITUTE

Ana iya cire haruffa tare da sanarwa mai sauƙi da ake kira SUBSTITUTE. Gabaɗaya hangen mai aiki: = SUBSTITUTE (rubutu, old_text, sabon_rubutu, lambar shigarwa).

• Rubutu - a nan an saita filin tare da bayanan da za a canza.
• Old_text shine bayanan da zasu canza.
• New_text – bayanan da za a saka a maimakon na asali.
• shigarwa_number hujja ce ta zaɓi. Yana ba ku damar maye gurbin haruffa farawa da takamaiman lamba.

Misali, idan muna buƙatar aiwatar da cire wuraren da ke hannun hagu na babban rubutu, to muna buƙatar shigar da dabara mai zuwa: = MUSAMMAN (A1;".";"").

Amfani da wannan dabara, za mu maye gurbin da aka bayar, rubuta zuwa hagu na babban rubutu, da sarari. Yanzu muna buƙatar aiwatar da kawar da waɗannan wurare. Don aiwatar da wannan hanya, ana amfani da mai aiki, wanda ke da sunan TRIM. Ayyukan yana ba ku damar nemo wuraren da ba dole ba kuma cire su. Babban ra'ayi na ma'aikaci yayi kama da haka: = KYAUTA ().

Muhimmin! Wannan dabarar tana cire wuraren al'ada kawai. Misali, idan mai amfani ya ƙara bayanin da aka kwafi daga wasu rukunin yanar gizon zuwa takaddar aiki, to bazai ƙunshi sarari ba, amma haruffa kamanninsu. A wannan yanayin, ma'aikacin TRIM ba zai yi aiki don gogewa ba. Anan zaka buƙaci amfani da Nemo da Cire kayan aiki.

Ana sharewa tare da afaretan CLEAN

Zabi, zaka iya amfani da afaretan PRINT. Babban ra'ayi na afareta don cire haruffan da ba za a iya bugawa ba yayi kama da haka: = TSAFTA(). Wannan aikin yana kawar da haruffan da ba a buga su ba a cikin layi (Ratsewar layi, haruffa sakin layi, murabba'ai daban-daban, da sauransu). Mai aiki yana da mahimmanci a lokuta inda ake buƙatar aiwatar da cirewar layin layi.

Muhimmin! Mai aiki yana cire yawancin ƙarin haruffa kawai.

Ƙarshe da ƙarshe game da kawar da haruffan farko

Mun yi la'akari da hanyoyin cire haruffan farko daga bayanan tabular. Hanyoyin suna nuna amfani da haɗin gwiwar masu aiki. Yin amfani da ayyuka yana ba ku damar hanzarta aiwatar da aiki tare da babban adadin bayanan tabular.