Yuav ua li cas tshem tawm cov kab tawg (cov tsheb thauj mus los) los ntawm cov hlwb hauv Excel 2013, 2010 thiab 2007

Qhov kev qhia no yuav qhia koj txog peb txoj hauv kev los tshem tawm cov tsheb thauj mus los ntawm cov hlwb hauv Excel. Koj tseem yuav kawm txog yuav hloov cov kab tawg nrog lwm cov cim. Tag nrho cov lus pom zoo ua haujlwm hauv Excel 2013, 2010, 2007 thiab 2003.

Kab tawg tuaj yeem tshwm hauv cov ntawv rau ntau yam laj thawj. Feem ntau cov tsheb thauj mus los tshwm sim hauv phau ntawv ua haujlwm, piv txwv li thaum cov ntawv luam tawm ntawm nplooj ntawv web, thaum lawv twb nyob hauv phau ntawv ua haujlwm tau txais los ntawm tus neeg siv khoom, lossis thaum peb tus kheej ntxiv rau lawv los ntawm nias lub pob. Alt + Nkag mus.

Txawm yog vim li cas rau lawv, qhov kev sib tw tam sim no yog tshem tawm cov tsheb thauj mus los, vim tias lawv cuam tshuam nrog cov lus tshawb nrhiav thiab ua rau cov kab clutter thaum qhwv tau qhib.

Tag nrho peb txoj kev nthuav tawm yog ceev heev. Xaiv ib qho uas haum koj tshaj plaws:

  • Peb tshem tawm tag nrho cov kab tawg manually kom ceev cov khoom hauv kev txiav txim ntawm ib daim ntawv.
  • Peb tshem tawm cov kab tawg siv cov qauv thiab yog li teeb tsa txoj kev ua cov ntaub ntawv hauv cov hlwb.
  • Peb siv VBA macro kom tshem cov kab tawg los ntawm ntau phau ntawv ua haujlwm.

Nco ntsoov: Thaum xub thawj, cov ntsiab lus "Carriage return" thiab "Line feed" tau siv thaum ua haujlwm ntawm lub tshuab ntaus ntawv thiab qhia txog ob txoj haujlwm sib txawv. Ib tus neeg nyeem uas xav paub ntau ntxiv tuaj yeem nrhiav cov ntaub ntawv ntxaws ntxaws txog qhov no hauv Is Taws Nem.

Cov khoos phis tawj thiab cov lus ua software tau tsim nrog cov yam ntxwv ntawm cov tshuab ntaus ntawv hauv siab. Qhov no yog vim li cas ob lub cim uas tsis tau luam tawm tam sim no tau siv los qhia txog kab tawg: tsheb rov qab (Kev thauj mus los, CR lossis ASCII code 13) thiab Kab txhais lus (Line pub, LF lossis ASCII code 10). Ntawm Windows, ob lub cim tau siv ua ke, thiab ntawm * NIX systems, tsuas yog siv cov kab tshiab xwb.

Ua tib zoo saib xyuas: Ob qho kev xaiv muaj nyob hauv Excel. Thaum importing los ntawm cov ntaub ntawv .txt or . Csv cov ntaub ntawv feem ntau muaj carriage rov qab thiab kab noj. Thaum ib kab so nkag tau manually los ntawm nias Alt + Nkag mus, Excel tsuas yog ntxig tus cim tshiab. Yog cov ntaub ntawv . Csv tau txais los ntawm tus kiv cua ntawm Linux, Unix lossis lwm yam zoo sib xws, tom qab ntawd npaj rau kev ntsib nrog tsuas yog tus cim tshiab.

Tshem cov carriage rov manually

pros: Txoj kev no yog qhov ceev tshaj plaws.

cons: Tsis muaj nyiaj ntxiv 🙁

Qhov no yog li cas koj tuaj yeem tshem tawm cov kab tawg siv "Nrhiav thiab hloov":

  1. Xaiv tag nrho cov hlwb uas koj xav tshem tawm cov tsheb thauj mus los lossis hloov lawv nrog lwm tus cim.Yuav ua li cas tshem tawm cov kab tawg (cov tsheb thauj mus los) los ntawm cov hlwb hauv Excel 2013, 2010 thiab 2007
  2. Xovxwm Ctrl + Hmus nqa ib lub dialog box Nrhiav thiab hloov (Nrhiav thiab Hloov).
  3. Muab tus cursor tso rau hauv qhov chaw Yuav kom pom (Nrhiav dab tsi) thiab nias Ctrl + J.. Thaum xub thawj siab ib muag, daim teb yuav zoo li khoob, tab sis yog tias koj saib ze, koj yuav pom ib qho me me hauv nws.
  4. Nyob rau hauv lub Hloov los ntawm (Hloov Nrog) nkag mus rau txhua tus nqi los ntxig rau hauv qhov chaw ntawm lub tsheb thauj mus los. Feem ntau ib qho chaw yog siv rau qhov no kom tsis txhob muaj kev sib tsoo ntawm ob lo lus uas nyob ib sab. Yog tias koj tsuas yog xav tshem tawm cov kab tawg, tawm hauv qhov chaw Hloov los ntawm (Hloov Nrog) khoob.Yuav ua li cas tshem tawm cov kab tawg (cov tsheb thauj mus los) los ntawm cov hlwb hauv Excel 2013, 2010 thiab 2007
  5. xovxwm Hloov tag nrho (Hloov tag nrho) thiab txaus siab rau qhov tshwm sim!Yuav ua li cas tshem tawm cov kab tawg (cov tsheb thauj mus los) los ntawm cov hlwb hauv Excel 2013, 2010 thiab 2007

Tshem cov kab tawg siv Excel cov qauv

pros: Koj tuaj yeem siv cov qauv sib txuas los yog zes rau kev txheeb xyuas cov ntawv nyuaj hauv cov txheej txheem ntawm tes. Piv txwv li, koj tuaj yeem tshem tawm cov tsheb thauj mus los thiab tom qab ntawd nrhiav qhov chaw ua ntej lossis tom qab, lossis qhov chaw ntxiv ntawm cov lus.

Qee qhov xwm txheej, cov kab tawg yuav tsum raug tshem tawm kom thiaj li siv cov ntawv tom qab los ua cov lus sib cav tsis muaj kev hloov pauv rau thawj cov hlwb. Cov txiaj ntsig tuaj yeem siv tau, piv txwv li, ua haujlwm sib cav Saib (LOOKUP).

cons: Koj yuav tsum tau tsim ib pawg pab thiab ua ntau yam ntxiv.

  1. Ntxiv ib kab ntawv pab rau qhov kawg ntawm cov ntaub ntawv. Hauv peb qhov piv txwv, nws yuav raug hu 1 kab.
  2. Hauv thawj lub xovtooj ntawm pawg pabcuam (C2), nkag mus rau cov qauv kom tshem tawm / hloov cov kab tawg. Hauv qab no yog ob peb cov qauv siv tau rau ntau lub sijhawm:
    • Cov mis no haum rau siv nrog Windows thiab UNIX carriage rov / kab noj ua ke.

      =ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);"")

      =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")

    • Cov qauv hauv qab no yog qhov tsim nyog rau kev hloov kab tawg nrog lwm tus cim (piv txwv li, “,” – comma + chaw). Hauv qhov no, cov kab yuav tsis raug muab ua ke thiab cov chaw ntxiv yuav tsis tshwm sim.

      =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);", ")

      =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")

    • Thiab qhov no yog li cas koj tuaj yeem tshem tawm tag nrho cov cim tsis tau luam tawm ntawm cov ntawv nyeem, suav nrog cov kab tawg:

      =ПЕЧСИМВ(B2)

      =CLEAN(B2)

    Yuav ua li cas tshem tawm cov kab tawg (cov tsheb thauj mus los) los ntawm cov hlwb hauv Excel 2013, 2010 thiab 2007

  3. Luam cov qauv rau txhua lub hlwb hauv kab.
  4. Optionally, koj tuaj yeem hloov qhov qub kem nrog ib qho tshiab, nrog cov kab tawg tawm:
    • Xaiv tag nrho cov cell hauv ib kab C thiab nias Ctrl + C luam cov ntaub ntawv rau hauv daim ntawv teev cia.
    • Tom ntej no, xaiv ib lub cell B2, nias lub keyboard shortcut Ua haujlwm + F10 thiab ces Insert (Ntxig).
    • Rho tawm tus pab pawg.

Tshem cov kab tawg nrog VBA macro

pros: Tsim ib zaug - siv dua thiab ua dua nrog txhua phau ntawv ua haujlwm.

cons: Yam tsawg kawg yuav tsum muaj kev paub txog VBA.

VBA macro hauv qhov piv txwv hauv qab no tshem tawm cov khoom thauj rov qab los ntawm txhua lub hlwb ntawm daim ntawv ua haujlwm.

Sub RemoveCarriageReturns() Dim MyRange As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Rau Txhua MyRange Hauv ActiveSheet.UsedRange Yog 0 < InStr(MyRange, Chr(10)), "Tom qab ntawd MyRange = Hloov Chr(MyRang(10) ") Xaus Yog Daim Ntawv Thov Tom ntej.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

Yog tias koj tsis paub VBA dhau lawm, kuv xav kom koj kawm cov kab lus yuav ua li cas ntxig thiab ua tiav VBA code hauv Excel.

Sau ntawv cia Ncua