How to Add a Character in Excel to Multiple Cells (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to know how to add a character in Excel to multiple cells? Then, this is the right place for you. Here, you will find 5 different ways to add a character in Excel to multiple cells.


Add a Character in Excel to Multiple Cells: 5 Ways

Here, we have a dataset containing the values of Student IDs and Names of some students. Now, we will show you how to add a character in Excel to multiple cells using this dataset.

5 Ways to Add a Character in Excel to Multiple Cells


1. Using CONCATENATE & CHAR Functions to Add a Character to Multiple Cells

In the first method, we will show you how to add a character in Excel to multiple cells using the CONCATENATE and CHAR functions. The CONCATENATE function is used to join text from multiple cells, and the CHAR function is used to get a specific character in return of a given value between 1 to 255. Here, we will create Passwords containing the Student ID and Name of each student and a colon between them.

Using CONCATENATE & CHAR Functions to Add a Character to Multiple Cells

Follow the steps given below to do it on your own.

Steps:

  • In the beginning, select cell D5.
  • Then, insert the following formula.
=CONCATENATE(B5,CHAR(58),C5)

Here, we added a colon, cell B5, and Cell C5 using the CONCATENATE function. The colon is added by using the CHAR function.

  • Now, press ENTER.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Using CONCATENATE & CHAR Functions to Add a Character to Multiple Cells

  • Finally, you will get the Passwords with a character using the CONCATENATE and CHAR functions.


2. Use of Ampersand (&) Operator to Add a Character to Multiple Cells

We can also use the Ampersand (&) operator to add a character to multiple cells in Excel. Go through the given steps below to do it on your own.

Steps:

  • Firstly, select cell D5.
  • Then, insert the following formula.
=B5&":"&C5

Use of Ampersand (&) Operator to Add a Character to Multiple Cells

Here, we added a colon, Cell B5, and C5 in the formula using the Ampersand (&) operator.

  • After that, press ENTER.
  • Next, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

  • Finally, you will get the Passwords with a character using the Ampersand (&) operator.

Use of Ampersand (&) Operator to Add a Character to Multiple Cells


3. Applying Flash Fill Command to Add a Character in Excel to Multiple Cells

Next, we will show you how to apply the Flash Fill feature to add a character to multiple cells in Excel. Follow the steps given below to do it on your own dataset.

Steps:

  • In the beginning, select cell D5.
  • Then, type 1501001:James as Password.

Applying Flash Fill Command to Add a Character in Excel to Multiple Cells

  • After that, go to the Data tab >> click on Flash Fill.

  • Finally, you will get the Passwords with a character using the Flash Fill feature.

Applying Flash Fill Command to Add a Character in Excel to Multiple Cells


4. Using Format Cells Feature to Add a Character to Multiple Cells

Now, we will use the Format Cells feature to add a character to multiple cells in Excel. Follow the steps given below to do it on your own dataset.

Steps:

  • Firstly, select Cell range B5:B11.
  • Then, press CTRL+C to copy the Cell range.

Using Format Cells Feature to Add a Character to Multiple Cells

  • Next, select Cell D5.
  • After that, press CTRL+V to paste the cell range.

  • Now, Right-click on the cell range.
  • Then, select Format Cells.

Using Format Cells Feature to Add a Character to Multiple Cells

  • Now, the Format Cells box will appear.
  • After that, go to the Custom option.
  • Then, type \$# in the Type box.
  • Next, click on OK.

  • Finally, you will get the Passwords with a character using the Format Cells feature.

Using Format Cells Feature to Add a Character to Multiple Cells


5. Using VBA to Add a Character in Excel to Multiple Cells

In the final method, we will use VBA to add a character to multiple cells in Excel. Here, we will use the Names of the students to make a Password.

Using VBA to Add a Character in Excel to Multiple Cells

Go through the steps below to do it on your own.

Steps:

  • Firstly, go to the Developer tab >> click on Visual Basic.

  • Now, the Microsoft Visual Basic for Applications will open.
  • After that, go to the Insert tab >> select Module.

Using VBA to Add a Character in Excel to Multiple Cells

  • Then, write the following code in your Module.
Sub Add_Character()

    Dim row_number As Integer
    For row_number = 5 To 11
        If Cells(row_number, 2).Value <> "" _
        Then Cells(row_number, 3).Value = "#" & Cells(row_number, 2).Value
    Next row_number

End Sub

Code Breakdown

  • First, we created a Sub Procedure as Add_Character.
  • Next, we declared row_number as Integer.
  • After that, we used a For loop for each row_number from 5 to 11.
  • Then, we used the IF function to check if there is any value in the row_number then added # with the row_number.

  • Next, click on the Save button and go back to your worksheet.

Using VBA to Add a Character in Excel to Multiple Cells

  • Now, select the Cell range C5:C11.
  • After that, go to the Developer tab >> click on Macros.

  • Now, the Macros box will appear.
  • Then, select Add_Character.
  • After that, click on Run.

Using VBA to Add a Character in Excel to Multiple Cells

  • Finally, you will get the Passwords with a character using VBA.


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Download Practice Workbook


Conclusion

So, in this article, you will find detailed steps to add a character in Excel to multiple cells. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.


<< Go Back to Characters | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo