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

or

Conas téacs a chur le tús nó deireadh gach cealla in Excel?

Uaireanta b’fhéidir go mbeidh ort an téacs sonrach céanna a chur le tús nó deireadh gach cealla i rogha. Má chuir tú an téacs le gach cill de láimh ceann ar cheann, caithfidh sé go leor ama. An bhfuil bealaí níos éasca ann? Sea, tá go leor cleas éasca ann an téacs céanna a chur le tús nó deireadh gach cealla i rogha.

Cuir téacs sonraithe le tús / deireadh gach cealla le foirmlí

Cuir téacs sonraithe le tús / deireadh gach cealla le VBA

Cuir téacs sonraithe le tús / deireadh gach ells le Kutools for Excel smaoineamh maith3

Cuir téacs sonraithe le suíomh sonraithe na gcealla go léir le Kutools for Excel smaoineamh maith3


Cuir téacs sonraithe le tús / deireadh gach cealla le foirmlí

Tá dhá fhoirmle ann chun téacs sonraithe a chur le tús nó deireadh gach cealla roghnaithe i Microsoft Excel.

Modh 1: & foirmle

Cuir isteach an = "Aicme A:" & A2 i gCill C2, agus ansin tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a chur i bhfeidhm. Agus an téacs sonrach curtha leis roimh na cealla go léir, féach an scáileán:

doc téacs sonrach a chur leis 1

Modh 2: Foirmle concatenate

Cuir isteach an = Concatenate ("Aicme A:", A2) i gCill C2, agus ansin tarraing agus cóipeáil an fhoirmle seo chuig na cealla is mian leat a úsáid, féach an scáileán:

doc téacs sonrach a chur leis 2

Nótaí:

1. Más mian leat téacs sonraithe eile a chur leis i ngach cill, ní gá ach ceann nua a chur ina ionad Aicme A: le do théacs sa dá fhoirmle.

2. Foirmlí = A2 & ": Aicme A" agus = Concatenate (A2, ": Aicme A") cuirfidh : Aicme A. ag deireadh na gcealla.

3. Ní dhéanfaidh an dá fhoirmle an t-ábhar sa roghnú a mhodhnú go díreach.


cuir an téacs céanna le suíomh ar leith de gach cill in Excel

Conas a d’fhéadfá téacs nó carachtair a chur le tús ilchealla, nó téacs nó carachtair a chur le deireadh cealla nó téacs nó carachtair a chur isteach idir an téacs atá ann cheana? Le Cuir Téacs fóntais Kutools le haghaidh Excel, is féidir leat na hoibríochtaí seo a leanas a chur i bhfeidhm go tapa :. Cliceáil le haghaidh trialach saor in aisce lán-chuimsithe i 30 lá!
doc cuir téacs 6
 
Kutools for Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú go hiomlán 30 lá amach anseo.

Cuir téacs sonraithe le tús / deireadh gach cealla le VBA

Más mian leat téacs sonraithe a chur i ngach cealla roghnaithe go díreach, maolóidh an Macra VBA seo a leanas do chuid oibre.

1. Roghnaigh an raon ina gcuirfidh tú téacs sonraithe leis;

2. Coinnigh síos an Eile + F11 eochracha in Excel, agus osclaíonn sé an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

3. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód VBA seo a leanas i bhFuinneog an Mhodúil.

VBA: Téacs sonraithe a chur leis ag tús gach cille:

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "CN- " & c.Value 
Next
End Sub

4. Brúigh F5 eochair chun an macra seo a rith. Agus cuirfear an luach leis na cealla go léir CN- roimh ábhar na cille.

doc téacs sonrach a chur leis 3

Nótaí: 1. Chun téacs sonrach a chur leis ag deireadh gach cille, cuir an cód VBA seo a leanas i bhfeidhm.

VBA: Téacs sonraithe a chur leis ag deireadh gach cille:

Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
If c.value <> "" Then c.value = c.value & "-CN" 
Next
End Sub

Agus gheobhaidh tú an toradh seo a leanas:

doc téacs sonrach a chur leis 4

2. is féidir leat an athróg a athrú "CN-" nó "-CN"de na cóid thuas.


Cuir téacs sonraithe le tús / deireadh gach cealla le Kutools for Excel

Kutools le haghaidh Excel's Add Text cuideoidh uirlis leat téacs sonraithe a chur go tapa le tús nó deireadh gach cille i rogha.

Kutools le haghaidh Excel, le níos mó ná 300 feidhmeanna úsáideacha, déanann sé do phoist níos éasca. 

Tar éis a shuiteáil Kutools for Excel, déan mar atá thíos le do thoil:Download Kutools Íoslódáil saor in aisce do Excel Anois!)

1. Roghnaigh an raon ina gcuirfidh tú téacs sonraithe leis.

2. Cliceáil ar an Kutools > Text > Add Text…. Féach an pictiúr:

doc téacs sonrach a chur leis 11

3. i Add Text bosca dialóige, cuir isteach an téacs a chaithfidh tú a chur sa Text bosca.

(1.) Má dhéanann tú seiceáil Before first character ó na Position alt, agus cuirfear an téacs sonrach os comhair na luachanna cille go léir, féach an scáileán:

doc téacs sonrach a chur leis 6

(2.) Má dhéanann tú seiceáil After last character ó na Position alt, agus cuirfear an téacs sonrach leis ag deireadh na luachanna cille, féach an scáileán:

doc téacs sonrach a chur leis 7

Nótaí:

1. Kutools le haghaidh Excel's Add Text Ligeann uirlis duit réamhamharc a dhéanamh ar na hathruithe sa roghnú sa chuid Réamhamhairc.

2. Má sheiceálann tú an Skip non-text cells rogha, ní chuirfidh an uirlis seo an téacs sonraithe i gcealla a bhfuil ábhar nach téacs iad.


Cuir téacs sonraithe le suíomh sonraithe na gcealla go léir le Kutools for Excel

Iarratas a dhéanamh Kutools for Excel's Add Text feidhm, ní amháin gur féidir leat an téacs sonraithe a chur le tús nó deireadh na gcealla, ach is féidir leat an téacs sonraithe a chur le suíomh sonraithe na gcealla.

Tar éis a shuiteáil Kutools for Excel, déan mar atá thíos le do thoil:Download Kutools Íoslódáil saor in aisce do Excel Anois!)

1. Roghnaigh raon ar mhaith leat téacs a chur leis, agus ansin cliceáil Kutools > Text > Add Text.

2. An Add Text taispeánfar dialóg, agus cuir isteach an téacs sonraithe agus sonraigh an seasamh áirithe ar mhaith leat an téacs a chur isteach sna boscaí. Féach an pictiúr:

Seo mé cineál 3 isteach sa Specify ciallaíonn bosca téacs téacs a chur leis tar éis tríú carachtar na sreinge.

doc téacs sonrach a chur leis 8

3. Cliceáil Ok or Apply. Cuireadh an téacs sonraithe le suíomh sonraithe na gcealla. Féach an pictiúr:

doc téacs sonrach a chur leis 9

Leid:

(1) In Specify bosca téacs, is féidir leat uimhreacha le camóg a chlóscríobh ar leithligh chun téacs a chur in áit iolrach ag an am céanna.
doc téacs sonrach a chur leis 12

(2) Más mian leat téacs a chur roimh gach litir uachtarach, déanfaidh an 1st letter is uppercase rogha i Add Text is féidir le fóntais cabhrú leat.
doc téacs sonrach a chur leis 10

Chomh maith leis sin, is féidir leat téacs a chur roimh gach litir litreach íochtair, nó gach litir uimhreach.

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


Taispeántas: Cuir téacs le tús nó deireadh gach cealla


Earraí coibhneasta:


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.
    zach · 1 years ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    Salar · 1 years ago
    the VBA code worked perfectly on Office for Mac
  • To post as a guest, your comment is unpublished.
    Marco · 1 years ago
    Me has quitado varias horas de trabajo de encima, mil gracias!!!
  • To post as a guest, your comment is unpublished.
    Sunny · 1 years ago
    @Amanda Hi, Amanda, if you want to convert date from mm/dd/yyyy to dd/mm/yyyy, this article https://www.extendoffice.com/documents/excel/4646-excel-convert-dd-mm-yyyy-to-mm-dd-yyyy.html can help you, it list two easiest ways for solving this job.
  • To post as a guest, your comment is unpublished.
    dkbhardwaj · 1 years ago
    thanks a lot dear for sharing information.
  • To post as a guest, your comment is unpublished.
    Courtney · 1 years ago
    Thank you! Apend and pre-pend macro works great
  • To post as a guest, your comment is unpublished.
    Amanda · 1 years ago
    I am trying to change a date formula from mm/dd/yyyy to dd/mm/yyyy. The leading zeros for months and dates less than 10 (two digits) are going away. Anyone have tips on how to easily put them back. I tried using the =month, =day, =year formulas and concatenating them. However, the leading zeros are dropping off. I would manually have to put in the leading zeros. Is there an easy way to do this? We are going to have approximately 1,000 rows of data on our file each month.
  • To post as a guest, your comment is unpublished.
    Sunny · 2 years ago
    @Manik Hi, Manik, use this =IF(B1>0, "Profit", "Loss")
  • To post as a guest, your comment is unpublished.
    Sunny · 2 years ago
    @m.omarhayat@gmail.com Hello, sorry to read your question so late. Here is a solution but you need to free download Kutools for Excel.

    Using the Convert Formula to Text utility to convert the formula cell to text, then apply Add Text utility to add the text string in the Specify position(1), then click Ok. After all cells have been added text, conver them to formula by click Kutools > Content > Convert Text to Formula.
  • To post as a guest, your comment is unpublished.
    Manik · 2 years ago
    I want to make a formula that show in cell text "Profit" when another cell show +digit, and show in that cell "Loss" when another that cell show -digit
  • To post as a guest, your comment is unpublished.
    deepakksingh90@gmail.com · 2 years ago
    @Raghulan Use if conditation with concadiate like when chek len<9 then concdinate("$$$",FL1000),if(Len(FL1000)>8,concdinate("$$",FL1000) then repetedhte valuse
  • To post as a guest, your comment is unpublished.
    AK · 2 years ago
    Thanks a lot!! I was initially using Access but this is so much easier.
  • To post as a guest, your comment is unpublished.
    m.omarhayat@gmail.com · 3 years ago
    Hello, i want to add text in a formula;

    current cell value: ='DAM91-SVC'!$C$47
    i want to add text: [MHSV Sales Report 2017 (JAN-DEC).xlsx]

    therefore the cell value should read like this in the end: ='[MHSV Sales Report 2017 (JAN-DEC).xlsx]DAM91-SVC'!$C$47

    please tell me how will this be done.
    thank you
    total 1800 cell count need alteration.
  • To post as a guest, your comment is unpublished.
    Raghulan · 3 years ago
    Hi I want to add $ symbol to a field value, to even the length of 9 chars.
    Below is my requirement, please help


    FL1000 -- > $$$FL1000
    FL10003 --> $$FL10003
  • To post as a guest, your comment is unpublished.
    Gaine Faste · 3 years ago
    Sub AppendToExistingOnLeft()
    Dim c As Range
    For Each c In Selection
    If c.Value <> "" Then c.Value = "' " & c.Value
    ActiveCell.Offset(1, 0).Range("A1").Select
    Next

    End Sub


    Great formula, with the addition of the 5th line, it automatically switches to the next cell, providing users with the ability to use a "Ctrl + " ", to populate a large amount of data by just holding down "Ctrl+ "(any common letter of choice) ".
  • To post as a guest, your comment is unpublished.
    Oladayo Olarewaju · 4 years ago
    @Andrew Jensson This really works. How can i register on this forum?
  • To post as a guest, your comment is unpublished.
    Leo · 4 years ago
    Um... I know the basics of Excel, definitely didn't know anything about the Microsoft Visual screen and these steps were so crystal clear to follow. I was able to create my first macro, which made the 400 accounts i needed to do this to, a BILLION times easier.

    Thank you!
  • To post as a guest, your comment is unpublished.
    RBTRIPP · 4 years ago
    Thank you for the VBA. It saved me a ton of time and effort.
  • To post as a guest, your comment is unpublished.
    Shaikh · 4 years ago
    Thank you so much. Thank once again!
  • To post as a guest, your comment is unpublished.
    Qasim · 4 years ago
    I want to display this number in percentage Exp: Volume Achieved-KG 30%

    but all it is coming is Volume Achieved-KG 30 any one can help me on this.

    ="Volume Achieved-KG "&ROUND(SUM($H$20*100),0)
  • To post as a guest, your comment is unpublished.
    VISHNU VASUDEVAN · 4 years ago
    Awesome! I am try this last day, and its works smoothly without any error...thanks for your assist..
  • To post as a guest, your comment is unpublished.
    sania · 4 years ago
    Cool. Thanks!! Works perfectly
  • To post as a guest, your comment is unpublished.
    Tanya · 4 years ago
    thanks it's great code.
    but i need some help.
    How to change this code that the change will takes effect only on cells that contain text and not numbers
  • To post as a guest, your comment is unpublished.
    Tanya · 4 years ago
    hello,
    thanks it's great code.
    but i need some help.
    How to change this code that the change will takes effect only on cells that contain text and not numbers
  • To post as a guest, your comment is unpublished.
    Michael Kane · 4 years ago
    thanks for this. You guys are legeneds
  • To post as a guest, your comment is unpublished.
    rakshitha · 4 years ago
    Thanks, adding specific letters before and after helped !!!!
  • To post as a guest, your comment is unpublished.
    Bill Bond · 5 years ago
    Many Thanks for taking the time to provide this code.
    It added some text to the beginning of every cell in a big selected column.
    It did exactly what I wanted in a few seconds.
    What was REALLY useful as well, was showing me how to actually run the code in a simple manner.
    I always thought you had to add a control then add the code into the Control's event, etc.

    Best Regards, Bill
  • To post as a guest, your comment is unpublished.
    RESHMA · 5 years ago
    i have such data and i want to give them numbers in front of them serially,
    E.G
    data result
    reshma - reshma1
    reshma - reshma2
    ritesh - ritesh1
    ritesh - ritesh2
    ritesh - ritesh 2
    sam - sam 1
    sam - sam 2
    rakesh - rakesh 1
    neha - neha 1
    neha - neha 2
    neha - neha 3
  • To post as a guest, your comment is unpublished.
    vinser · 5 years ago
    thanks a lot

    its was easy to work
  • To post as a guest, your comment is unpublished.
    Ritesh saxena · 5 years ago
    VBA is Awesome. Excellent tip. Saved huge amount of time. Thank you so much..
  • To post as a guest, your comment is unpublished.
    Fabio · 5 years ago
    The VBA trick works like a charm, no wwird formulas hanging around my sheet. Thanks a million!
  • To post as a guest, your comment is unpublished.
    reza · 5 years ago
    hi
    i want have following cells with order number and differnt cell content:

    skdljfkldf 1
    dfkgjfdgkjldkfj2
    lkgj 3
    dkjfdfjkj 4
    kjglkjgkjgrjtgrjtgr 5
  • To post as a guest, your comment is unpublished.
    sameer · 5 years ago
    i need help on below
    1.i want to add specific letters in cell -for eg: below

    in cell A1 i have 111 then after when i click button add AB infront of 111,if already any letters in front of number then no need to add AB

    2.How to restrict use to enter space and restrict length min 5 and max 6
  • To post as a guest, your comment is unpublished.
    Davy · 5 years ago
    Thank you very. It really has helped me very much. Please keep up the good work you are providing
  • To post as a guest, your comment is unpublished.
    Aayush · 5 years ago
    thank you so much. the information given above is very benficial
  • To post as a guest, your comment is unpublished.
    amr · 5 years ago
    thanks that helped a lot
    thanks again :roll:
  • To post as a guest, your comment is unpublished.
    algae · 6 years ago
    hi,
    my workbook has 60 sheets. i want to add a column A in sheets 5 to 56 and insert the name of the worksheet in each cell of col A . Each sheet has 150 rows, so 150 cells A1-A150.

    would greatly appreciate a vba soln

    tnx
  • To post as a guest, your comment is unpublished.
    scott aramaki · 6 years ago
    This was EXTREMELY helpful. And I don't use all caps very often.
  • To post as a guest, your comment is unpublished.
    Deepak Sadanand · 6 years ago
    Thank you. Helped with what I was looking for.
  • To post as a guest, your comment is unpublished.
    Shaun · 6 years ago
    I am trying the VBA, says its running. But its for 63,000 rows. Will this take a ton of time (an hour so far). That normal?
  • To post as a guest, your comment is unpublished.
    Miss Bunny · 6 years ago
    Thanks mch . It helped me a lot :)
  • To post as a guest, your comment is unpublished.
    Niranjan · 6 years ago
    where is the comment i have posted earlier
  • To post as a guest, your comment is unpublished.
    Niranjan · 6 years ago
    Hey,
    this is great. I need something more than this . can I select several rows and perform the same action on alternate cells.
    for example:if I had add hello at the end of each alternate cell
    a
    b
    c
    d
    e
    what I need is

    a hello
    b
    c hello
    d
    e hello
  • To post as a guest, your comment is unpublished.
    Arun Prasad Kumar · 6 years ago
    If you want to add a particular 'word' in all cells in between somewhere in the text, simply we can use "copy & replace" (Ctrl+F) function by selecting all the cells to be replaced in MS Excel.
    For example, if you have to insert "_code" in all the cells in a particular column containing cells having values like Species_Class1, Species_Class2,.....Species_Class100 and change them to Species_code_Class1, Species_code_Class2,.....Species_code_Class100.
    CLick "Crtl+F" --> Use Replace option.
    In 'Find what' give "Species*_Class" and in 'Replace with' give "Species_code_Class".
    Then click "Relace All".
    Cheers.....

    Arun Prasad, India
  • To post as a guest, your comment is unpublished.
    kirsten · 6 years ago
    Hey with the VBA method of adding information to the right of the already entered information, is there a way that you can only do it for selected cells and not ALL cells. I am trying to filter and run the macro on those cells only, and when I do and unfilter it still puts it on all. Thanks!
  • To post as a guest, your comment is unpublished.
    Bharathi · 6 years ago
    Excellent tip. Saved huge amount of time. Thank you so much..
  • To post as a guest, your comment is unpublished.
    handojim · 6 years ago
    Is it possible to change the text string that you're adding to the target cell to be a cell reference, so that instead of adding "LBA " to the start of each cell, you add the contents of a different cell?
    For example if I want to append the contents of cells A1:A10 to the start of the existing text in cells B1:B10??
  • To post as a guest, your comment is unpublished.
    Robert Bollinger · 6 years ago
    KU tools is so cool!! It took me .3 seconds to add the text I needed.

    Robert
  • To post as a guest, your comment is unpublished.
    Joseph kishore · 7 years ago
    Needed Help,


    actually am trying to find all numeric and before that add a paragraph mark..? would you please tell me how to done this this kutools in word or excel...?
  • To post as a guest, your comment is unpublished.
    Steve Watkins · 7 years ago
    VBA is fantastic, so powerful...a great help