Skip to main content

Conas téacsanna a bhaint roimh charachtar ar leith nó ina dhiaidh sin ó chealla in Excel?

Tugann an t-alt seo modhanna éagsúla isteach chun téacsanna a bhaint roimh charachtar ar leith nó tar éis an naoú carachtar tarlaithe ó chealla in Excel.


Scrios téacsanna roimh charachtar sonrach nó ina dhiaidh sin le Aimsigh agus Ionadaigh in Excel

Chun gach téacs a bhaint roimh charachtar ar leith nó ina dhiaidh sin leis an bhfeidhm Aimsigh agus Ionadaigh, déan mar a leanas le do thoil.

1. Roghnaigh na cealla a bhainfidh tú téacsanna roimh charachtar ar leith nó dá éis, brúigh Ctrl + H eochracha a oscailt Aimsigh agus Ionadaigh dialóg.

Sa bhosca dialóige Aimsigh agus Ionadaigh:

1. Chun gach rud a bhaint roimh an gcarachtar sonrach mar chamóg, clóscríobh le do thoil *, isteach sa Aimsigh cad Bosca téacs;

2. Chun gach rud a bhaint tar éis an charachtair shonraigh ar nós camóg, clóscríobh le do thoil ,* isteach sa Aimsigh cad Bosca téacs;

Nótaí:

1. Féadfaidh tú an camóg a athrú go carachtar ar bith de réir mar is gá duit.

2. Sa chás seo, bainfear na téacsanna go léir roimh an camóg deireanach nó tar éis an chéad camóg ó na cealla roghnaithe.

2. Coinnigh an Ionadaigh le bosca téacs folamh, agus ansin cliceáil ar an Ionadaigh uile cnaipe. Féach an pictiúr:


Bain go héasca gach carachtar uimhriúil, neamh-uimhriúil nó sonraithe ó chealla in Excel

Kutools le haghaidh Excel's Bain Carachtair cuidíonn fóntais leat gach carachtar uimhriúil, neamh-uimhriúil nó sonraithe a bhaint as cealla roghnaithe in Excel.
Íoslódáil rian iomlán saor in aisce 30 lá de Kutools le haghaidh Excel anois!


Scrios téacsanna roimh nó tar éis an chéad charachtair shonraigh / dheireanaigh de réir foirmle

Taispeánfaidh an chuid seo foirmlí duit chun gach rud a scriosadh roimh an gcéad charachtar sonrach / deireanach nó dá éis ó chealla in Excel.

Gach rud a bhaint roimh an gcéad camóg, le do thoil:

Roghnaigh cill bhán, cóipeáil agus greamaigh an fhoirmle thíos inti, agus brúigh Iontráil eochair. Ansin tarraing an Líon Láimhseáil an fhoirmle a chur i bhfeidhm ar chealla eile. Féach an pictiúr:

Foirmle: Bain gach rud roimh an gcéad camóg

CEART (B5, LEN (B5) -FIND (",",B5))

Nótaí:

1. San fhoirmle thuas, is é B5 an chill a mbainfidh tú téacsanna as, agus is é "," an carachtar a mbainfidh tú téacsanna bunaithe air.

2. Chun gach ceann a bhaint roimh an gcarachtar sonrach deireanach, bain úsáid as an bhfoirmle seo:

CEART (B5, LEN (B5) -FIND ("@", SUBSTITUTE (B5,"Carachtar"," @ ", (LEN (B5) -LEN (SUBSTITUTE (B5,"Carachtar"," "))) / LEN ("Carachtar"))))

Gach rud a bhaint tar éis an chéad camóg, le do thoil:

Roghnaigh cill bhán, cóipeáil agus greamaigh an fhoirmle thíos inti, agus brúigh Iontráil eochair. Ansin tarraing an Líon Láimhseáil an fhoirmle a chur i bhfeidhm ar chealla eile. Féach an pictiúr:

Foirmle: Bain gach rud tar éis an chéad camóg

= LEFT (B5, FIND (",", B5) -1)

Nótaí:

1. San fhoirmle thuas, is é B5 an chill a mbainfidh tú téacsanna as, agus is é "," an carachtar a mbainfidh tú téacsanna bunaithe air.

2. Chun gach ceann a bhaint tar éis an charachtair shonraigh dheireanaigh, bain úsáid as an bhfoirmle seo:

LEFT (B5, DEIREADH ("@", SUBSTITUTE (B5,"carachtar"," @ ", LEN (B5) -LEN (SUBSTITUTE (B5,"carachtar"," ")))) - 1)


Scrios téacsanna roimh nó tar éis an naoú carachtar tarlú de réir foirmle

Is féidir leis na foirmlí thíos cuidiú le gach ceann a scriosadh roimh nó tar éis an naoú carachtar tarlaithe ó chealla in Excel.

Gach rud a bhaint roimh an naoú carachtar tarlaithe ó chealla, tá ort:

Roghnaigh cill bhán chun an toradh a aschur, cóipeáil an fhoirmle thíos isteach ann, agus brúigh Iontráil eochair. Ansin tarraing an Líon Láimhseáil an fhoirmle a chur i bhfeidhm ar chealla eile. Féach an pictiúr:

Foirmle: Bain gach rud roimh an dara camóg tarlú

CEART (SUBSTITUTE (B5, ",", CHAR (9), 2), LEN (B5) - DEIREADH (CHAR (9), SUBSTITUTE (B5, ",", CHAR (9), 2), 1) + 1)

Nótaí:

1. Sna foirmlí, B5, ","agus 2 ciallaíonn uimhir go mbainfear an t-ábhar go léir tar éis an dara camóg tarlaithe ó chill B5.

2. Is féidir leat an ","agus 2 uimhir chuig carachtar agus uimhir suímh ar bith a theastaíonn uait.

Gach rud a bhaint tar éis an naoú carachtar tarlaithe ó chealla, tá ort:

Roghnaigh cill bhán chun an toradh a aschur, cóipeáil an fhoirmle thíos isteach ann, agus brúigh Iontráil eochair. Ansin tarraing an Líon Láimhseáil an fhoirmle a chur i bhfeidhm ar chealla eile. Féach an pictiúr:


Foirmle: Bain gach rud tar éis an dara camóg tarlú

= LEFT (SUBSTITUTE (B5,",", CHAR (9),2), DEIREADH (CHAR (9), SUBSTITUTE (B5,",", CHAR (9),2), 1) -1)

Nótaí:

1. Sna foirmlí, B5, ","agus 2 ciallaíonn uimhir go mbainfear an t-ábhar go léir tar éis an dara camóg tarlaithe ó chill A7.

2. Is féidir leat an ","agus 2 uimhir chuig carachtar agus uimhir suímh ar bith a theastaíonn uait.


Bain téacsanna go héasca roimh / i ndiaidh carachtar ar leith le Kutools for Excel

Mura bhfuil ach deighilteoir camóg amháin ann do gach cill i raon, agus má theastaíonn uait gach rud a bhaint roimh an camóg seo nó dá éis, bain triail as an Cealla Scoilt fóntais de Kutools le haghaidh Excel. Cuideoidh an fóntais seo leat an fhadhb a réiteach gan ach cúpla cad a tharlaíonn:

1. Roghnaigh na cealla a mbainfidh tú gach rud roimh an camóg nó dá éis, agus ansin cliceáil Kutools > Téacs > Cealla Scoilt. Féach an pictiúr:

2. Sa Cealla Scoilt dialóg, roghnaigh an Scoilt go Colúin rogha sa cineál alt, agus sa Scoilt ag alt, roghnaigh an Eile rogha agus clóscríobh camóg isteach sa bhosca bán, agus ansin cliceáil ar an OK cnaipe. Féach an pictiúr:

3. Eile Cealla Scoilt tagann dialóg suas, roghnaigh cill bhán chun na téacsanna a aimsiú, agus ansin cliceáil ar an OK cnaipe.

Ansin feiceann tú go roinntear na cealla roghnaithe de réir carachtar ar leith - camóg. Féach an pictiúr:

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.


Kutools le haghaidh Excel - Cabhraíonn sé leat an obair a chríochnú i gcónaí roimh an am, Bíodh níos mó ama agat chun taitneamh a bhaint as an saol
An minic a bhíonn tú ag imirt teacht suas leis an obair, easpa ama le caitheamh ort féin agus ag do mhuintir?  Kutools le haghaidh Excel cabhrú leat chun déileáil leis 80% Excel puzzles agus feabhas a chur ar éifeachtacht oibre 80%, tabhair níos mó ama duit aire a thabhairt do theaghlach agus taitneamh a bhaint as an saol.
300 uirlis chun cinn le haghaidh 1500 cás oibre, déan do phost an oiread sin níos éasca ná riamh.
Ní gá foirmlí agus cóid VBA a chur de ghlanmheabhair a thuilleadh, tabhair sosa d’inchinn as seo amach.
Is féidir oibríochtaí casta agus arís agus arís eile a phróiseáil aon-uaire i soicindí.
Laghdaigh na mílte oibríochtaí méarchláir & luch gach lá, slán a fhágáil le galair cheirde anois.
Bí i do shaineolaí Excel i gceann 3 nóiméad, cabhrú leat aitheantas tapa a fháil agus ardú céime pá a fháil.
110,000 duine an-éifeachtach agus 300+ rogha cuideachtaí a bhfuil cáil dhomhanda orthu.
Déan do $ 39.0 ar fiú níos mó ná $ 4000.0 oiliúint daoine eile.
Triail saor in aisce gné iomlán 30 lá. Ráthaíocht Airgead Ar Ais 60 Lá gan chúis.

Comments (60)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
السلام عليكم كيفكم اسمي احمد ابراهيم محمد علامي من ذوي الاعاقة اصم وابكم الثانوية وحاسوب من الرياض جازان صبيا رقم جوالي قديم اشويه 0501601641 ابغي مساعدة فايدة وشكرا لكم جميعا أن شاءالله
This comment was minimized by the moderator on the site
رقمي 0501601641 وشكرا لكم جميعا أن شاءالله السلام عليكم كيفكم اسمي احمد ابراهيم محمد علامي
This comment was minimized by the moderator on the site
Hi how can i split or add spacing in between each textRoominCoolDecoApartmentinBrunswickEast
This comment was minimized by the moderator on the site
Hi z,If every word in the sentence starts with a capital letter, you can try one of the methods below to get it done:1. To add space between each word, methods in this article can do you a favor:How To Insert Spaces Before Capital Letters In Excel?
https://www.extendoffice.com/documents/excel/1864-excel-insert-space-before-capital-letter.html

2. To split text by capital letter in the sentence, you can try the methods in this article.How To Split Text Into Separate Columns By Capital Letter In Excel?
https://www.extendoffice.com/documents/excel/3336-excel-split-text-by-capital-letter.html
This comment was minimized by the moderator on the site
Hi I have the next text:

/credit-loan/super-credit/- from 1251 to 1016( -18.78%)
/credit-loan/no-credit/- from 1018 to 703( -30.94%)
/loan-loan-credit-cards- from 773 to 633( -18.11%)
/loan-loan/fair-loan/- from 321 to 502( -29.69%)

I need to delete everything after "-" before the from, including the character "-" so the above to become:

/credit-loan/super-credit/
/credit-loan/no-credit/
/loan-loan-credit-cards
/loan-loan/fair-loan/

And after I would also need to have the above without the "/" as in (but I think I can manage that later):

/credit-loan/super-credit
/credit-loan/no-credit
/loan-loan-credit-cards
/loan-loan/fair-loan
This comment was minimized by the moderator on the site
Hiimy texts is following
Execution/428/2019DOI:05-07-20192 years 5 months 4
daysCompliance23-09-2019

I want to delete whole texts after DOI:
This comment was minimized by the moderator on the site
Hi I have the next text:
/credit-loan/super-credit/- from 1251 to 1016( -18.78%)
/credit-loan/no-credit/- from 1018 to 703( -30.94%)
/loan-loan-credit-cards- from 773 to 633( -18.11%)
/loan-loan/fair-loan/- from 321 to 502( -29.69%)

I need to delete everything after "-" before the from, including the character "-" so the above to become:
/credit-loan/super-credit/
/credit-loan/no-credit/
/loan-loan-credit-cards
/loan-loan/fair-loan/

And after I would also need to have the above without the "/" as in (but I think I can manage that later):
/credit-loan/super-credit
/credit-loan/no-credit
/loan-loan-credit-cards
/loan-loan/fair-loan
This comment was minimized by the moderator on the site
Thank you!
This comment was minimized by the moderator on the site
What is the formula to keep only 160USCAIRAPPW01 in below excel spreadsheet?

16780187,160-USC-NOV-Updates-Server,160 - ALL LAC+USC Servers,160USCAIRAPPW01,Non-compliant,11/23/2019 11:33 AM,,,,,,16891741,Non-compliant,0X00000000,Success,11/23/2019 11:22 AM,(SYSTEM),Yes,No,Yes,
This comment was minimized by the moderator on the site
remove everything before
the last hyphen?
BABU RAM (622)
JAGAN NATH
This comment was minimized by the moderator on the site
I have: E4U19-31C20010093021EI 3032AER LINGUS 190805000010. I need to remove everything before the first space and everything after the last space but I need to keep the spaces. I have this formula but it removes the spaces: =LEFT(RIGHT(A19,LEN(A19)-FIND(" ",A19)),FIND("^^",SUBSTITUTE(RIGHT(A19,LEN(A19)-FIND(" ",A19))," ","^^",LEN(RIGHT(A19,LEN(A19)-FIND(" ",A19)))-LEN(SUBSTITUTE(RIGHT(A19,LEN(A19)-FIND(" ",A19))," ",""))))-1)
This comment was minimized by the moderator on the site
Hi, please try this formula:
=LEFT(RIGHT(B9,LEN(B9)-FIND(" ",B9)+1),FIND("^^",SUBSTITUTE(RIGHT(B9,LEN(B9)-FIND(" ",B9))," ","^^",LEN(RIGHT(B9,LEN(B9)-FIND(" ",B9)))-LEN(SUBSTITUTE(RIGHT(B9,LEN(B9)-FIND(" ",B9))," ",""))))-1)
This comment was minimized by the moderator on the site
Great buddy. Too much helpful post.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations