Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas carachtair neamhuimhreacha a bhaint as cealla in Excel?

Má cheaptar go bhfuil go leor sonraí den sórt sin agat ABCD4578124YUIOH, agus níl uait ach na carachtair neamhuimhreacha a bhaint  ABCDYUIOH ach coinnigh na carachtair uimhriúla i gcealla. Ar ndóigh, is féidir leat na carachtair sin a bhaint ceann ar cheann, ach ar dheis anseo is féidir leat fáil réidh leis na carachtair neamhuimhreacha ó chealla go tapa mar seo a leanas:

Bain carachtair neamh-uimhriúla le cód VBA

Uirlis áisiúil chun carachtair neamh-uimhriúla a bhaint le cliceáil amháin


Bain carachtair neamh-uimhriúla le cód VBA

Chun carachtair neamh-uimhriúla a bhaint de raon le cód VBA, déan mar a leanas le do thoil:

1. cliceáil Forbróir > Visual Basic a oscailt Microsoft Visual Basic d’iarratais fuinneog. Sa Microsoft Visual Basic d’iarratais fuinneog, cliceáil Ionsáigh > Modúil, agus ansin na cóid seo a leanas a chóipeáil agus a ghreamú isteach sa mhodúl:

VBA: Bain gach carachtar neamh-uimhriúil

Sub RemoveNotNum()
'Updateby Extendoffice
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 "[0-9]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

2. Ansin cliceáil ar an cnaipe chun an cód a rith, Sa popping up KutoolsforExcel bosca dialóige, roghnaigh raon leis na carachtair neamhuimhreacha is mian leat a bhaint, agus ansin cliceáil ar an OK cnaipe. Féach an pictiúr:

bain carachtair neamh-uimhriúla 1 chóip

3. Ansin baintear gach carachtar neamh-uimhriúil sa raon roghnaithe.

bain carachtair neamh-uimhriúla 2

Má tá na huimhreacha le pointí deachúil, is féidir leat an VBA seo a leanas a úsáid:

Sub RemoveNotNum()
'Updateby Extendoffice
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 "[0-9.]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

Is féidir leat na torthaí a fheiceáil thíos mar a thaispeántar ar an scáileán:

bain carachtair neamh-uimhriúla 3


Uirlis áisiúil chun carachtair neamh-uimhriúla a bhaint le cliceáil amháin

Chun carachtair neamh-uimhriúla a bhaint i raon, Kutools le haghaidh Excel'S Bain Carachtair is féidir le fóntais é a dhéanamh le cliceáil amháin.

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á. 

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar atá thíos le do thoil:

1. Aibhsigh an raon is mian leat carachtair neamh-uimhriúla a bhaint. Cliceáil Kutools > Téacs > Bain Carachtair.

bain carachtair neamh-uimhriúla 4

2. Sa an Bain Carachtair bosca dialóige, seiceáil an Neamh-uimhriúil rogha sa Bain Carachtair alt, agus ansin cliceáil ar an OK or Cuir iarratas isteach cnaipe. Agus baintear na carachtair neamh-uimhriúla den roghnú láithreach. Féach an pictiúr:

bain carachtair neamh-uimhriúla 5

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

Chun tuilleadh eolais a fháil faoi Bain Carachtair de Kutools le haghaidh Excel, tabhair cuairt le do thoil Bain tuairisc ar ghnéithe carachtair.


Taispeántas: Bain carachtair uimhriúla, aibítreacha, neamh-inphriontáilte nó alfa-uimhriúla ó chealla

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!

Earraí gaolmhara:


Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

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á!
bun officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Abi DC · 4 years ago
    HI Thanks for the coding, i am getting the syntax error highlighted on For i = 1 To Len(Rng.Value) and the debug Sub RemoveNotNum() is highlighted in yellow.. kindly advice.
  • To post as a guest, your comment is unpublished.
    usuc · 4 years ago
    The VBA works great. I modified it to always work on a column, as shown:

    For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)

    xOut = ""

    For i = 1 To Len(Cell.Value)

    xTemp = Mid(Cell.Value, i, 1)

    If xTemp Like "[0-9]" Then

    xStr = xTemp

    Else

    xStr = ""

    End If

    xOut = xOut & xStr

    Next i

    Cell.Value = xOut

    Next

    I also used this to sort a column by the last digit, changing the last part to Cell.Value = Right(xOut, 1) then sorting it numerically.
  • To post as a guest, your comment is unpublished.
    Naresh kumar · 4 years ago
    Send me it on my email id
    • To post as a guest, your comment is unpublished.
      samola · 4 years ago
      Hi,

      I want to use this macro but want to pre-define a range. Can you please let me know how to pre-define the range.

      Thanks,
      Samit
  • To post as a guest, your comment is unpublished.
    Cole · 6 years ago
    Just wanted to let you know that if the string starts with zero(s), or starts with letters, followed by zero(s), followed by the rest of the string this will remove all of the beginning zeroes. This is weird because it doesn't remove the zero(s) if they are between other non-zero numbers within the string, only if they start the string or are the first numbers after the initial letters in a string.

    Example.

    0060100 would come out as 60100

    PFF057726 would come out as 57726.

    Let me know if you have an explanation for this and can think of a solution. Thank you.
  • To post as a guest, your comment is unpublished.
    Roger · 6 years ago
    Thanx Guys for such a nice work. The script is awesome and it worked for. Keep it up guys.
  • To post as a guest, your comment is unpublished.
    Easter2015 · 6 years ago
    Use this Formaula for replacing the existing character with the new one
    =SUBSTITUTE(text,old_text,new_text,[instance_num])
    for ex-=SUBSTITUTE(TRIM(G1),"/","")
    Wish U all Happy Easter 2015
  • To post as a guest, your comment is unpublished.
    amanda · 7 years ago
    I received an error when I hit the > run button in the developer:

    Compile Error:
    Expected:end of statement


    and the word "non" in non-numeric is highlighted
  • To post as a guest, your comment is unpublished.
    Daniel · 7 years ago
    Wow. that worked , exactly what I needed.

    Thanks,
  • To post as a guest, your comment is unpublished.
    Julie Hodnett · 7 years ago
    Awesome Worked GREAT!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Bryan Steven · 7 years ago
    wouldn't be better to replace the input-box method by just setting
    WorkRng like this:
    Set WorkRng = Intersect(ActiveSheet.UsedRange, Selection)
    that way if user select a entire column it wouldn't generate any error.
  • To post as a guest, your comment is unpublished.
    jo · 7 years ago
    thankyou worked well
  • To post as a guest, your comment is unpublished.
    JW · 7 years ago
    sample code for comments