Tag: Concatenation Operator

Adding Comma Character at End of Cells in Excel

If you frequently work with Excel spreadsheets, you may need to add a comma character at the end of cells for various reasons, such as creating lists or formatting data for import/export.

This post will guide you on how to add a comma at the end of cells in Excel using different methods. One way to achieve this is by using the concatenation operator “&” or the CONCATENATE function. Alternatively, you can use VBA code to automate the process and save time when dealing with large amounts of data.

1. Adding Comma Character at End of Cells using Concatenation Operator

If you want to add a comma character at end of cells in a range, you just need to use the concatenation operator to create a formula. Like this:

=B1&","

Type this formula in a blank cell, and then press Enter key. And then drag the AutoFill handle over other cells to apply this formula.

add comma at end of cells1

You will see that the comma character has been added at the end of all cells.

2. Adding Comma Character at End of Cells Using CONCATENATE Function

You can also add a comma character at the end of cells in excel using CONCATENATE function, you can following these steps:

Step1: Select one blank cell where you want to add the comma.

Step2: type the formula:

=CONCATENATE(cell reference,",")

Step3: Replace “cell reference” with the reference to the cell that you want to add the comma to. Press “Enter” to complete the formula.

For example, if you want to add a comma at the end of the text in cell B1, the formula would be:

=CONCATENATE(B1,",")
Adding Comma Character at End of Cells 10.png

 Once you enter this formula, the text in cell A1 will be displayed with a comma at the end.

Step4: You can also use this function to add a comma at the end of multiple cells at once by dragging the formula down to the other cells you want to include.

Adding Comma Character at End of Cells 11.png

3. Adding Comma Character at End of Cells with VBA Code

If you want to add a comma character at the end of cells in Excel using VBA code, you can follow these steps:

Step1: Open the Visual Basic Editor by pressing Alt + F11.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBA Editor, insert a new module by going to “Insert” > “Module“.

Adding Comma Character at End of Cells vba1.png

Step3: In the module, paste the following code:

Adding Comma Character at End of Cells vba1.png
Sub AddCommaToEnd_ExcelHow()
    'Declare variables
    Dim sourceRange As Range
    Dim destRange As Range
    Dim cell As Range
    
    'Prompt user to select source range
    Set sourceRange = Application.InputBox("Select source range", Type:=8)
    
    'Loop through each cell in the source range
    For Each cell In sourceRange
        'Add comma to end of cell value
        cell.Value = cell.Value & ","
    Next cell
End Sub

Step4: run the code by clicking the “Run” button (or pressing F5) or press ALT +F8 to open Macro dialog box. Select Macro name as you need. Clicking on Run button.

Adding Comma Character at End of Cells vba2.png

Step5: When the code runs, it will prompt the user to select a range of cells to modify.

Adding Comma Character at End of Cells vba3.png

Step5: The Comma Character would be added at the end of each cell in the selected range.

Adding Comma Character at End of Cells vba4.png

4. Video: Adding Comma Character at End of Cells

In this video, you will learn how to add a comma character at the end of cells in Excel using different methods such as concatenation operator, CONCATENATE function, and VBA Code.

How to Generate Random Passwords in Excel

This post will teach you how to quickly generate a random password with Formula in Excel. You can combine a series of worksheet functions to create a complex formula to generate a random password in Microsoft Excel. How do I create an random code generator in Excel.

1. Video: Generate Random Passwords or Letters in Excel

This tutorial video provides step-by-step instructions on how to use Excel’s built-in functions to generate random passwords or letters in Excel, making it easy to create secure and unique passwords for a variety of purposes.

2. Generate Random Password

If you want to generate a random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula.

Just use the following steps:

Step1: Select one cell that you want to generate the random password,

Step2: then enter this formula in that Cell

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(42,43))

Step3: press Enter key to apply this formula. You will see that it will generate an eight characters password.

generate random passwords1

3. Random Letter Generator in Excel

If you want to create a series of random letters or strings in Microsoft Excel Spreadsheet. and you can use built-in functions such as the CHAR function, which can convert a number to its corresponding ASCII character.

For example, to generate a 5-character string of uppercase letters, and you can use the following formula based on the CHAR function and the RANDBETWEEN function. 

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
How to Generate Random Passwords in Excel 1.png

This formula generates a random uppercase letter for each position in the string. By copying and pasting the formula into multiple cells, users can generate a series of unique random strings for various purposes, such as generating temporary passwords or unique IDs.

4. Generate Random Uppercase Letter

If you want to generate a random password only contain alpha characters (random uppercase letter string), you can use the following formula:

=CHAR(RANDBETWEEN(65,90))
generate random passwords2

5. Generate Random Lowercase Letter String

If you want only to generate a random lowercase letter string, you can use the below formula:

=CHAR(RANDBETWEEN(97,122))
generate random passwords3

6. Generate Random Password Only Contain Numeric Characters

If you want to generate a random password only contain 6-digit numeric characters, you can use the following formula:

=RANDBETWEEN(100000,900000)
generate random passwords4

7. Generate Random Symbol String

If you want to generate a random symbol string, just use the following formula:

=CHAR(RANDBETWEEN(33,47))
generate random passwords5

This formula will randomly generate one of the following specific characters: !”#$%&'()*+,-./

We talked that the CHAR function will generate lots of specific characters, and if you just want to create a password that can be type on the keyboard, so you should keep the specific character simple and avoid to use characters like ¶, Œ, or ©.

8. Conclusion

Generating random passwords in Excel can be a quick and convenient way to create secure and unique passwords for various purposes. By using a combination of built-in functions, users can generate passwords of different lengths and complexity levels, as well as customize them to include different character sets.

9. Related Functions

  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

Combing the First and Last Names

This post will guide you how to quickly combine the first and the last names into one single cell in an Excel Worksheet. How do I combine first and last name columns in Excel.

Combing the First and Last Names

Assuming that you have a list of data in the range of cells B1:C4 and one column contains the first names and another column contains the last names. And how to combine two columns of first and last names in your worksheet. You can use the concatenate operator to create a formula to join those two names. Like this:

=B2 & " "&C2

Type this formula into the formula box of cell D2, and drag the AutoFill Handle over other cells to join the first and last names.

combing first and last names1

You can also use the CONCATENATE function to create a formula to achieve the same result.  Like this:

=CONCATENATE(B2," ",C2)

Type this formula into a blank cell and then press Enter key in your keyboard, and drag the AutoFill handle over other cells.

combing first and last names2

Combining Date and Time into One Cell

This post will guide you how to combine a data column and time column into one column in Excel. How do I quickly combine data and time in the different cells into one cell in your Worksheet. For example, if you have two columns and one column contain date values and another column contain time values, and you want to combine date cell and time cell into one single cell, how to achieve it. Let’s see the following detailed steps.

Combining Date and Time into One Cell

To combine the date in one cell and time in another cell in Excel, you can use the TEXT function in combination with the concatenation operator to create a new formula. Like this:

=TEXT(B1,"m/dd/yy ")&TEXT(C1,"hh:mm:ss")

You need to type this formula into a blank cell and then press Enter key in your keyboard, and drag the AutoFill Handle over other cells to apply this formula to combine date and time.

The First Text function will format the text string based on the date format provided in the second argument. And the second Text function will format the text string based on the time format provided in the second argument.

Let’s see the result:

combining date and time2

You can also directly use add operation to combine date and time values from the different cells into one single cell. Like this:

=B1 + C1

Type this formula into a blank cell, and then press Enter key.

combining date and time1

Related Functions

  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

How to Add Prefix or suffix to Cell

This post will guide you how to add common prefix or suffix to all cells or the range of cells in your worksheet in Excel. How do I add the same prefix or suffix to a range of cells without any changing the existing numbers or text in the range. How to add prefix or suffix to all cells with Excel VBA Macro.

Assuming that you have a list of data that you want to add common text to the starting position of all cells, of course, you can do it by manually, but it will consume lots of time and it is not a good idea. And you can use the concatenate operator, the CONCATENATE function to create a new Excel formula to add prefix or suffix to the range of cells that you selected. Or you can write a new Excel VBA Macro to achieve the same result.

Method 1: Using Concatenation Operator to add prefix or suffix

If you want to add prefix or same text to the starting of all cells in your range, you can use the Concatenation operator to build a formula, for example, you want to add same text “excelhow” to all cells in range A1:A4, then you can use the following formula:

="excelhow"&A1

Ether this formula in a blank cell, such as: B1, then drag the AutoFill Handle down to other cells to apply this formula.

add prefix or suffix 1

If you want to add suffix to all cells with concatenation operator, you can use the following formula:

=A1&"excelhow"

add prefix or suffix2

You will see that all cells are added the same prefix text in your worksheet.

Method 2: Using concatenation function to add prefix

To add prefix or suffix text or number to the range of cells with concatenation function, you can wirte down the following formula:

=CONCATENATE("excelhow",A1)
or
=CONCATENATE(A1,"excelhow")

add prefix or suffix3

Method 3: Using Excel VBA Macro to add prefix

You can also use the following VBA Macro code to add prefix or suffix in your worksheet in Excel.  Let’s see the below steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

add prefix to cell113

Sub AddPrefixToCell()
    Set W = Application.Selection
    Set W = Application.InputBox("select one Range that you want to add prefix to each cell:", "AddPrefixToCell", W.Address, Type:=8)
    S = Application.InputBox("Please type a prefix character:", "AddPrefixToCell", "", Type:=2)
    For Each R In W
        R.Value = S & R.Value
    Next
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run button.

add prefix or suffix5

6# select a range that you want to add prefix, click OK button, then add one prefix string or character. Click OK button.

add prefix to cell111

add prefix to cell112

7# you will see that the prefix is added to all range of cells.

add prefix or suffix8

If you want to add suffix to a range of cells with VBA macro in Excel, you can use the following VBA macro.

Sub AddSuffixToCell()
    Set W = Application.Selection
    Set W = Application.InputBox("select one Range that you want to add suffix to each cell:", "AddSuffixToCell", W.Address, Type:=8)
    S = Application.InputBox("Please type a prefix character:", "AddSuffixToCell", "", Type:=2)
    For Each R In W
        R.Value = R.Value & S
    Next
End Sub

add prefix or suffix9

Related Functions

  • Excel Concat function
    The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…

How to Add a Character or Letter before Each Word in a Cell

This post will guide you how to add a specified character or letter before each word in a cell in excel. How to add a character to each word in a cell using excel formula. How to add a hash character before each word with a formula.

Add a character or letter before each word

In the previous posts, we talked that how to add the same text or characters into the beginning of the first word for all cells. And How to add text or characters to the end of all cells in excel.

So How to add a character before each word in a cell? You can use a combination of the concatenate operator and the SUSTITUTE function to create an excel formula as follows:

="Character"&SUBSTITUTE(Cell, " "," Character")

Assuming that you would like to add a hash “#” sign before each word within a bunch of cells, such as: Cell B1, then you can write down the following formula based on the above generic formula:

="#"&SUBSTITUTE(B1, " "," #")

add character before each word1

The concatenate operator will join the hash sign before the first word in a cell. And the SUBSTITUTE function will replace all space characters with new character hash sign. So this formula will add the hash character before each word in Cell B1.

Note: if the text strings in one cell are separated by other specific character, such as: hash, comma, etc. and then you need to update the second argument in the SUBSTITUTE function from space character to another character.

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….

How to add text to the end all cells

In the previous post, we talked that how to add the same text into the beginning of the text in one cell or all selected cells. And this post will guide you that how to add the specified text or characters to the end of all cells in excel. How to create an excel formula to add same text string or characters to the end of text string in one Cell. How to create an excel macro to add specific text to the end of the text in all of cells.

Add text to the end of all cells with Formula

To add the specified text string or characters to the end of all selected cells in excel, you can use the concatenate operator or the CONCATENATE function to create an excel formula.

For example, if you want to add text “excel” into the end of the text in Cell B1, you can use the following excel formula:

= B1&" "& "excel"

OR

=CONCATENATE(“B1”,””,”excel”)

You can enter the above formulas in Cell C1, and then drag the fill handle down to other cells in column C and you will see that the specified text “excel” has been added into the end of the text string in B1.

add text to end of text1

add text to end of text1

Add text to the beginning of all cells with Excel VBA

You can create a new excel macro to add text string “excel” to the end of text in Cell B1 in Excel VBA, just refer to the below steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub addTextAtEndCell()
    Dim e as range
    For each e in Selection
        If e.value <> "" Then e.value = e.value & "excel"
    Next
End Sub

add text to end of text3

5# back to the current worksheet, then run the above excel macro, you will see that the specific text “excel” has been added into the end of the text in all selected Cells.

add text to end of text4

Related Formulas

Related Functions

  • Excel Concat function
    The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…

How to combine columns without losing data

This post will guide you how to merge and combine multiple columns without losing any data in excel. How do I merge columns without losing data using Excel Formula. For example, you want to join two columns that contain first name and last name into one column without losing data.

If you want to merge multiple columns that you selected and you can use the “Merge & Center” command, but there is one problem is that it just keep the upper-left most data only. so if you just want to merge multiple columns and don’t care about if the data is deleted or not.

Firstly, Click “Merge & Center” command under Alignment group in Home Tab.

combine columns1

then you will get a warning message that is “Merging cells only keeps the upper-left cell value, and discards the other values.”

combine columns1

Merge columns without losing data

How to keep all data after merging columns. You can use the concatenate operator or CONCATENATE function to create an excel formula. Assuming that you want to merge column B and C into column D, you can use the following formulas:

=B2&C2

OR

=CONCATENATE(B2, “ “ ,C2)

You can write the above formula in Cell D2, you will see that both column B and column C have been merged into column D without losing any data.

combine columns3-1

combine columns3-1

Then you can drag the fill handle to other cells in Column D to apply the above formula to merge other cells.

combine columns3

combine columns3

Related Formulas

Related Functions

  • Excel Concat function
    The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…

Related Posts

  • Combine multiple workbooks into one workbook
    If you want to combine multiple workbooks into one workbook, you need to open all workbooks, then determine the workbooks to merge and the destination of workbook.…
  • Merge multiple worksheets into one worksheet
    How to merge two or more excel worksheet into one worksheet by using some VBA code. You can create a new excel macro to combine multiple worksheets into one worksheet in Excel VBA…

How to join text from two or more cells into one cell separated by commas, space

This post explains that how to join multiple cells into one cell with commas or space character in excel.  How to combine text from two or more cells into one cell with space, commas or others specific characters as delimiter.

Join text from two or more cells with commas

You can merge text from two or more cells into one cell using a combination of the SUBSTITUTE function, the TRIM function and concatenation operator to create an excel formula.

For example, if you just want to combine cells from a range A1:C1 to a cell with commas, just use concatenation operator as follows:

=A1&", "&B1&", "&C1

join cells with commas1

If some of values in Cells are empty, you can use the concatenation operator to join text from all cells, and then you need to use TRIM function to remove all space character from text string except for single space between

All words. Then you can use the SUBSTITUTE function to replace all empty strings with comma.

So you can use the following excel formula:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1))

Let’s see how this formula works:

= A1&” “&B1&” “&C1

join cells with commas2

You will see that there are multiple empty string between words.

 

=TRIM(A1&” “&B1&” “&C1)

join cells with commas2

This formula removes empty strings from the text string and just leaves one space between all words.

 

=SUBSTITUTE(TRIM(A1&” “&B1&” “&C1),” “,”,”)

join cells with commas4

The SUBSTITUTE function replaces all space characters with commas.

Related Formulas

  • Split Multiple Lines from a Cell into Rows
    If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
  • The difference between Replace function and Substitute function in Excel
    There are two similar functions to replace text string in Excel. They are REPLACE and SUBSTITUTE functions. What’s the difference between REPLACE function and SUBSTITUTE function in excel 2013? .…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

 

How to Extract Text between Commas

This post will guide you how to extract text between commas in excel.

Before we talked that how to extract text between brackets using a formula with MID and SEARCH functions. And this post will extract text between commas instead of brackets. The biggest difference is that the brackets have the left and right, but the comma is not. So you can’t use the similar formula for commas.

To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function.

=SUBSTITUTE(MID(SUBSTITUTE("," & B1&REPT(" ",6), "," , REPT(",",255)),2*255,255), ",", " ")

Let’s see how this formula works:

=”,” & B1&REPT(” “,6)

extract text between commas 1

The REPT function will repeat empty string 6 times.

The concatenation operator (&) will join together one comma and text string in Cell B1 and another empty text string returned by REPT function to build a new text string. It will go into SUBSTITUTE function as its Text argument. This formula returns as a new text string like this:

,excel,word,ppt

 

= SUBSTITUTE(“,” & B1&REPT(” “,6), “,” , REPT(“,”,255))

The second argument of SUBSTITUTE function is set to a comma character as its Old_text argument.

The new_text value will use the result returned by the REPT function, it will repeat a comma character 255 times.

So this formula will replace all commas with new_text 255 commas in new text string.  The returned string is like this:

,,,,,,,,,,,,,,,,,excel,,,,,,,,,,,,,,,,,word,,,,,,,,,,,,,,,ppt        (255 commas between each word)

 

= MID(SUBSTITUTE(“,” & B1&REPT(” “,6), “,” , REPT(“,”,255)),2*255,255)

extract text between commas 3

This MID function will extract 255 characters from a text string (returned by the above SUBSTITUTE function) at a specific position 2*255.  So it returns like this:

,,,,,,word,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

 

=SUBSTITUTE(MID(SUBSTITUTE(“,” & B1&REPT(” “,6), “,” , REPT(“,”,255)),2*255,255),”,”,””)

extract text between commas 4

This formula will use a text string returned by the MID function as its Text argument and then replace all commas with the empty text string. So the last returned result is text that you want to extract.

Related Formulas

  • Extract Text between Parentheses
    If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
  • Extract Text between Brackets
    If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
  • Check If Cell Contains All Values from Range
    If you want to check if a cell contains all values in a list, you can use a combination of the SEARCH function, the SUMPRODUCT function, the ISNUMBER function and COUNTA function…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The REPT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the REPT function is as below:= REPT  (text, number_times)….