Skip to main content

Conas roinnt carachtair speisialta a bhaint as sreang in Excel?

Má tá carachtair speisialta ann mar % ^ & * () laistigh de na teaghráin téacs, agus anois, ba mhaith leat carachtair shonracha tráchtais a bhaint de na teaghráin chill. Tógfaidh sé go leor ama iad a bhaint ceann ar cheann, anseo, tabharfaidh mé isteach roinnt cleasanna gasta chun an tasc seo a réiteach in Excel.

Bain roinnt carachtair speisialta as sreangán téacs le Feidhm Sainmhínithe ag an Úsáideoir

Bain roinnt carachtair speisialta as sreangán téacs le Kutools le haghaidh Excel

mboilgeog cheart gorm saighead Bain roinnt carachtair speisialta as sreangán téacs le Feidhm Sainmhínithe ag an Úsáideoir

Is féidir leis an gcód VBA seo a leanas cabhrú leat na carachtair shonracha a theastaíonn uait a bhaint, déan mar a leanas:

1. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: Bain roinnt carachtair speisialta as sreangán téacs

Function RemoveSpecial(Str As String) As String
'updatebyExtendoffice 20160303
    Dim xChars As String
    Dim I As Long
    xChars = "#$%()^*&"
    For I = 1 To Len(xChars)
        Str = Replace$(Str, Mid$(xChars, I, 1), "")
    Next
    RemoveSpecial = Str
End Function

3. Ansin sábháil agus dún an cód seo, téigh ar ais chuig an mbileog oibre, agus iontráil an fhoirmle seo: = bainteach (A2) isteach i gcill bhán inar mian leat an toradh a chur, féach an pictiúr:

bain carachtair speisialta 1

4. Agus ansin tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a chur i bhfeidhm, agus na carachtair speisialta go léir nach dteastaíonn uait a bhaint den sreangán téacs, féach an scáileán:

bain carachtair speisialta 2

nótaí: Sa chód thuas, is féidir leat na carachtair speisialta a athrú # $% () ^ * & le haon duine eile ar mhaith leat a bhaint.

carachtair uimhriúla, alfa nó carachtair speisialta eile a bhaint as sreangán téacs

Kutools le haghaidh Excel's Bain Carachtair is féidir le gné cabhrú leat gach rud a bhaint go tapa uimhriúil, alfa, neamh-uimhriúil, neamh-alfa, neamhphriontáil, carachtair shonracha eile ó teaghráin téacs de réir mar is gá duit. Cliceáil chun Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!

bain carachtair speisialta 7

Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Mura bhfuil tú oilte leis an gcód VBA, Kutools le haghaidh Excel'S Bain Carachtair is féidir le fóntais cabhrú leat an tasc seo a chríochnú go tapa agus go héasca.

Tar éis a shuiteáil Kutools le haghaidh Excel, mar seo a leanas, le do thoil:

1. Roghnaigh na teaghráin téacs a theastaíonn uait roinnt carachtair speisialta a bhaint.

2. cliceáil Kutools > Téacs > Bain Carachtair, féach ar an scáileán:

3. Sa an Bain Carachtair bosca dialóige, seiceáil An Chustaim rogha faoin Bain Carachtair alt, agus iontráil na carachtair speisialta a theastaíonn uait a bhaint, féach an pictiúr:

bain carachtair speisialta 4

4. Agus ansin cliceáil Ok or Cuir iarratas isteach cnaipe, na carachtair a shonraigh tú sa An Chustaim Baineadh bosca téacs ó na teaghráin téacs ag an am céanna, féach scáileáin scáileáin:

bain carachtair speisialta 5 2 bain carachtair speisialta 6

Cliceáil chun Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!

Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (11)
No ratings yet. Be the first to rate!
Linny Anthony
Linny Anthony
This comment was minimized by the moderator on the site
Excelllent .. Five Stars.. thanks a lot
Reply
0
0
Cris
Cris
This comment was minimized by the moderator on the site
Thanks for this code
Function RemoveSpecial(Str As String) As String
'updatebyExtendoffice 20160303
Dim xChars As String
Dim I As Long
xChars = "#$%()^*&"
For I = 1 To Len(xChars)
Str = Replace$(Str, Mid$(xChars, I, 1), "")
Next
RemoveSpecial = Str
End Function

But it is possible that that can remove specific text? like "ab", "abc", "bc" and etc.
Reply
0
0
minhys1
minhys1
This comment was minimized by the moderator on the site
With Emoji need remove, how ?
Reply
0
0
gndecastro@gmail.com
This comment was minimized by the moderator on the site
Rather than searching for specific special characters, how about if you want to search for and replace ALL special characters. In other words, how would you write the search for NOT one of the following characters: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
Reply
Report
0
0
skyyang
skyyang    
This comment was minimized by the moderator on the site
Hi, Nick,
Do you want to remove all ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 characters from the cells?
Looking forward to your reply, thank you!
Reply
Report
0
0
gndecastro@gmail.com
    skyyang
This comment was minimized by the moderator on the site
No. I want to keep only "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
Reply
Report
0
0
skyyang
skyyang    
This comment was minimized by the moderator on the site
Nick,
Maybe the below VBA code can solve your problem, please try:

Sub RemoveNotAlphasNotNum()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
xOut = ""
For i = 1 To Len(Rng.Value)
xTemp = Mid(Rng.Value, i, 1)
If xTemp Like "[a-z.]" Or xTemp Like "[A-Z.]" Or xTemp Like "[0-9.]" Then
xStr = xTemp
Else
xStr = ""
End If
xOut = xOut & xStr
Next i
Rng.Value = xOut
Next
End Sub


Hope it can help you!
Reply
Report
0
0
Kim
Kim
This comment was minimized by the moderator on the site
Hmm I wonder, does it only work in the worksheet you've originally pasted the code in?
As for that one it only seems to work, not for any new workbook you open
Reply
0
0
skyyang
skyyang     Kim
This comment was minimized by the moderator on the site
Hello, Kim,
The VBA code can only applied in one workbook, if you want to apply it in a new workbook, you should copy and paste the code into your new workbook again.
Thank you!
Reply
Report
0
0
Kim
Kim
This comment was minimized by the moderator on the site
Hi guys,

I've applied the =removespecial(A2) code and it works perfectly in one worksheet but then in the other it gives me an invalid #NAME? error.
I checked the "format cells" and it's both on general and I've copied the same text + formula to both worksheets but it won't work.
Any clue what this might cause this?

Thanks and thank you so much for this code.
Saves me hours and hours of work!

Regards, Kim
Reply
0
0
Deepak
Deepak
This comment was minimized by the moderator on the site
Function GetWordWOSpecChar(Rng As Range)
'paste in VBA module, Use as a Formula
'Created by Deepak Sharma
Arr = Array("48", "49", "50", "51", "52", "53", "54", "55", _
"56", "57", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", _
"76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", _
"89", "90", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", _
"107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", _
"118", "119", "120", "121", "122")

For i = 1 To Len(Rng.Value)
txt = Mid(Rng.Value, i, 1)
For g = 1 To UBound(Arr)
If txt = Chr(Arr(g)) Then GetWord = Right(Rng.Value, Len(Rng.Value) - (i - 1)): Exit Function
Next g
Next i

End Function
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations