Excel String Concatenation

Excel string concatenation (i.e. joining together text strings in Excel) can be performed by either using using the Excel & Operator or using one of the built-in Excel functions (the Concatenate, Concat or Textjoin function).

These methods are each discussed below.

Excel String Concatenation Index:
Concatenate Using the & Operator
The Excel Concatenate, Concat and Textjoin Functions
Concatenate a Date and Time
Concatenate a Line Break
Concatenate Quotes

Excel String Concatenation Using the & Operator

When used between two text strings, the & Operator returns a single text string, consisting of the concatenation of the two original strings.

The spreadsheet below shows three examples of Excel string concatenation, using the & operator.

Note that the formulas in the above spreadsheet concatenate a space (» «) between the forenames and surnames.

The Excel Concatenate, Concat and Textjoin Functions

Excel provides three built-in functions to concatenate text strings. These are:

Concatenate Joins together two or more text strings (Replaced by the Concat function in Excel 2019)
Concat Joins together two or more text strings, or arrays of text strings (New in Excel 2019 — replaces the Concatenate function)
Textjoin Joins together two or more text strings, separated by a delimiter (New in Excel 2019) .

Note that the Concat function has replaced the Concatenate function in Excel 2019 (although the Concatenate function is still available in Excel 2019, for compatibility with older versions of Excel).

The only difference between these two functions is that the Concatenate function can only accept single text strings (or references to single cells containing text), whereas the Concat function can also accept arrays of text strings (or references to arrays of cells containing text).

Syntax of the Concatenate, Concat and Textjoin Functions

The syntax of the Concatenate function is:

where the text arguments are two or more text strings that you want to join together.

The syntax of the Concat function is:

where the text arguments are two or more text strings (or arrays of text strings) that you want to join together.

The syntax of the Textjoin function is:

  • The [delimiter] argument is an optional delimiter, to be inserted between each text string;
  • The [ignore_empty] argument is an optional logical value that specifies whether empty cells should be ignored (default value = TRUE);
  • The text arguments are two or more text strings (or arrays of text strings) that you want to join together.

Examples of the Concatenate, Concat and Textjoin Functions

The following spreadsheet shows three examples of Excel string concatenation, using the Concatenate, Concat and Textjoin functions.

Note that, in the above spreadsheet, a space is inserted between the forename and surname of the returned text string. This is supplied to the functions as:

  • One of the text arguments in the Concatenate and Concat functions;
  • The [delimiter] argument in the Textjoin function.

Concatenate a Date and Time

Dates and Times in Excel are stored internally as numbers. It is just the formatting of a cell that causes a number to be displayed as the date and/or time that it represents.

Интересно почитать:  Удалить пустые строки эксель

Therefore, if one of the elements of your Excel string concatenation is an Excel cell containing a date or time (or any other formatted number) it is the underlying number, that will be used in your your concatenated string, not the formatted date/time. An example of this is shown in the following spreadsheet:

Incorrect Concatenation of a Text String and a Date

If you want a date or a time (or any other formatted number in Excel) to form a part of your concatenated text string, you need to convert the number to a text string using the required formatting. This can be done using the Excel Text function.

An example of the correct concatenation of text and a date is shown below:

Note that the formula in the above example spreadsheet uses the Excel Text function, which receives a value (in this case the date in cell B2), and a formatting definition (in this case, the date format «dd-mmm-yyyy»), and returns a text string (in this example «29-Jan-1980»).

Concatenate a Line Break

A common problem that many users encounter, when using Excel string concatenation formulas, is how to insert a line break.

The easiest way to concatenate a line break is to use the Excel Char function. On most computer systems, the integer 10 represents the line break character. Therefore, the function CHAR(10) returns a line break. This is illustrated in the example spreadsheet below:

Note that, for a line break to be displayed in an Excel cell, the Wrap Text option must be enabled. In recent versions of Excel (2007 and later), this can be controlled by the Wrap Text button, which is located in the ‘Alignment’ group of the Home tab of the Excel ribbon (see below).

Excel Wrap Text Option in the Format Cells Dialog Box

Wrap Text Check Box in ‘Format Cells’ Dialog Box

The Wrap Text option can also be controlled from within the Format Cells dialog box. Therefore, if you have an older versions of Excel, which does not have the shortcut button, you can wrap text as follows:

    Select the cell(s) that you want to wrap text in;

Open the ‘Format Cells’ dialog box.

Concatenate Quotes in Excel

It can be confusing if you want to concatenate quotes in Excel, as Excel will interpret a double quotation mark as the start or end of a text string.

Method 1

In order to make Excel understand that you require the double quotes to be part of the final concatenated text, you need to use four double quote characters in your concatenation formula. For example:

returns the concatenated string Jim said «Hi».

Method 2

Alternatively, you can use the Excel Char function to specify the quotes character. On most computer systems, the integer 34 represents the double quotation mark character. Therefore, the function CHAR(34) returns a double quote and the example above becomes:

Интересно почитать:  Как выделить строки в excel по содержимому

MS Excel: How to use the CONCAT Function (WS)

This Excel tutorial explains how to use the Excel CONCAT function with syntax and examples.

Description

The Microsoft Excel CONCAT function allows you to join 2 or more strings together, including ranges of cells. It was released in Excel 2019 and replaces the CONCATENATE function.

The CONCAT function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the CONCAT function can be entered as part of a formula in a cell of a worksheet.

If you want to follow along with this tutorial, download the example spreadsheet.

Syntax

The syntax for the CONCAT function in Microsoft Excel is:

Parameters or Arguments

Returns

The CONCAT function returns a string/text value.

Applies To

  • Excel for Office 365, Excel 2019

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let’s look at some Excel CONCAT function examples and explore how to use the CONCAT function as a worksheet function in Microsoft Excel:

Excel CONCAT Function

Based on the Excel spreadsheet above, the following CONCAT examples would return:

Concatenate Space Characters

When you are concatenating values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results.

Let’s look at an easy example.

Based on the Excel spreadsheet above, we can concatenate a space character within the CONCAT function as follows:

In this example, we have used the second parameter within the CONCAT function to add a space character between the values in cell A2 and cell B2. This will prevent our values from being squished together.

Instead our result would appear as follows:

Here, we have concatenated the values from the two cells (A2 and B2), separated by a space character.

Concatenate Quotation Marks

Since the parameters within the CONCAT function are separated by quotation marks when they are string values, it isn’t straight forward how to add a quotation mark character within the result of the CONCAT function.

Here is an example that shows how to add a quotation mark to the start and end of the resulting string using the CONCAT function.

Based on the Excel spreadsheet above, we can concatenate a quotation mark to the front and the end as follows:

In this example, we have used the first parameter and fifth parameter within the CONCAT function to add a quotation mark to the start and end of the resulting string.

Since our parameters are enclosed in quotation marks, we use 2 additional quotation marks within the surrounding quotation marks to represent a quotation mark in our result as follows:

Then when you put the whole function call together:

You will get the following result:

Интересно почитать:  Как в excel настроить фильтр

Frequently Asked Questions

Question: For an IF statement in Excel, I want to combine text and a value.

For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).

I tried the following:

Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)

Answer: There are two ways that you can concatenate text and values. The first is by using the & character to concatenate:

Конкатенация строк в Python

Объединение строк – весьма всераспространенная операция в программировании. Объединение строк в Python быть может выполнено разными методами.

Мы можем выполнить конкатенацию строк последующими методами:

  • внедрение оператора+;
  • внедрение способа join();
  • внедрение оператора%;
  • внедрение функции format();
  • Внедрение f-строки (интерполяция буквальной строчки).

Конкатенация строк с внедрением оператора +

Это самый обычной метод конкатенации строк. Давайте поглядим на обычной пример.

Давайте поглядим на иной пример, где мы получим две строчки из пользовательского ввода и объединим их.

Конкатенация строк в Python

Весьма просто употреблять оператор + для конкатенации строк. Но аргументы должны быть строчкой.

Мы можем употреблять функцию str(), чтоб получить строковое представление объекта.

Давайте поглядим, как связать строчку с целым числом либо остальным объектом.

Наибольшая неувязка с оператором + состоит в том, что мы не можем добавить разделитель либо разделитель меж строчками. К примеру, если нам необходимо соединить «Hello» и «World» с разделителем пробелов, нам необходимо будет записать его как «Hello» + «» + «World».

Конкатенация строк в Python с внедрением функции join()

Мы можем употреблять функцию join() для объединения строчки с разделителем. Это полезно, когда у нас есть последовательность строк, к примеру перечень либо кортеж строк.

Если для вас не нужен разделитель, используйте функцию join() с пустой строчкой.

Конкатенация с внедрением оператора%

Мы можем употреблять оператор% для форматирования строк, он также может употребляться для конкатенации строк. Это полезно, когда мы желаем соединить строчки и выполнить обычное форматирование.

С внедрением функции format()

Мы также можем употреблять функцию string format() для объединения и форматирования строк.

Функция format() весьма мощная, внедрение ее лишь для конкатенации строк – неверное внедрение.

С внедрением f-строки

Если вы используете Python 3.6+, вы также сможете употреблять f-строку для конкатенации строк. Это новейший метод форматирования строк, представленный в PEP 498 – Literal String Interpolation.

F-строка в Python проще в написании по сопоставлению с функцией format(). Он также вызывает функцию str(), когда аргумент объекта употребляется в качестве подмены поля.

Заключение

Форматирование строчки Python можно выполнить несколькими методами. Используйте их в согласовании с вашими требованиями. Если для вас необходимо соединить последовательность строк с разделителями, используйте функцию join().

Если при конкатенации также требуется некое форматирование, используйте функцию format() либо f-строку. Направьте внимание, что f-строку можно употреблять с версиями Python 3.6 и выше.

Ссылка на основную публикацию
Adblock
detector