*Wondering how to add text to an existing cell in Excel? In this article, you will learn a few really simple ways to insert characters in any position in a cell.*

When working with text data in Excel, you may sometimes need to add the same text to existing cells to make things clearer. For example, you might want to put some prefix at the beginning of each cell, insert a special symbol at the end, or place certain text before a formula.

I guess everyone knows how to do this manually. This tutorial will teach you how to quickly add strings to multiple cells using formulas and automate the work with VBA or a special *Add Text* tool.

## Excel formulas to add text/character to cell

To add a specific character or text to an Excel cell, simply concatenate a string and a cell reference by using one of the following methods.

### Concatenation operator

The easiest way to add a text string to a cell is to use an ampersand character (&), which is the concatenation operator in Excel.

*text*"&

*cell*

This works in all versions of Excel 2007 - Excel 365.

### CONCATENATE function

The same result can be achieved with the help of the CONCATENATE function:

*text*",

*cell*)

The function is available in Excel for Microsoft 365, Excel 2019 - 2007.

### CONCAT function

To add text to cells in Excel 365, Excel 2019, and Excel Online, you can use the CONCAT function, which is a modern replacement of CONCATENATE:

*text*",

*cell*)

Note. Please pay attention that, in all formulas, *text* should be enclosed in quotation marks.

These are the general approaches, and the below examples show how to apply them in practice.

## How to add text to the beginning of cells

To add certain text or character to the beginning of a cell, here's what you need to do:

- In the cell where you want to output the result, type the equals sign (=).
- Type the desired text inside the quotation marks.
- Type an ampersand symbol (&).
- Select the cell to which the text shall be added, and press Enter.

Alternatively, you can supply your text string and cell reference as input parameters to the CONCATENATE or CONCAT function.

For example, to prepend the text "*Project:*" to a project name in A2, any of the below formulas will work.

In all Excel versions:

`="Project:"&A2`

`=CONCATENATE("Project:", A2)`

In Excel 365 and Excel 2019:

`=CONCAT("Project:", A2)`

Enter the formula in B2, drag it down the column, and you will have the same text inserted in all cells.

Tip. The above formulas join two strings without spaces. To separate values with a whitespace, type a **space character** at the end of the prepended text (e.g. "Project: ").

For convenience, you can input the target text in a predefined cell (E2) and **add two text cells together**:

Without spaces:

`=$E$2&A2`

`=CONCATENATE($E$2, A2)`

With spaces:

`=$E$2&" "&A2`

`=CONCATENATE($E$2, " ", A2)`

Please notice that the address of the cell containing the prepended text is locked with the $ sign, so that it won't shift when copying the formula down.

With this approach, you can easily change the added text in one place, without having to update every formula.

## How to add text to the end of cells in Excel

To append text or specific character to an existing cell, make use of the concatenation method again. The difference is in the order of the concatenated values: a cell reference is followed by a text string.

For instance, to add the string "*-US*" to the end of cell A2, these are the formulas to use:

`=A2&"-US"`

`=CONCATENATE(A2, "-US")`

`=CONCAT(A2, "-US")`

Alternatively, you can enter the text in some cell, and then join two cells with text together:

`=A2&$D$2`

`=CONCATENATE(A2, $D$2)`

Please remember to use an absolute reference for the appended text ($D$2) for the formula to copy correctly across the column.

## Add characters to beginning and end of a string

Knowing how to prepend and append text to an existing cell, there is nothing that would prevent you from using both techniques within one formula.

As an example, let's add the string "*Project:*" to the beginning and "*-US*" to the end of the existing text in A2.

`="Project:"&A2&"-US"`

`=CONCATENATE("Project:", A2, "-US")`

`=CONCAT("Project:", A2, "-US")`

With the strings input in separate cells, this works equally well:

## Combine text from two or more cells

To place values from multiple cells into one cell, concatenate the original cells by using the already familiar techniques: an ampersand symbol, CONCATENATE or CONCAT function.

For example, to combine values from columns A and B using a comma and a space (", ") for the delimiter, enter one of the below formulas in B2, and then drag it down the column.

Add text from two cells with an ampersand:

`=A2&", "&B2`

Combine text from two cells with CONCAT or CONCATENATE:

`=CONCATENATE(A2, ", ", B2)`

`=CONCAT(A2, ", ", B2)`

When adding **text from two columns**, be sure to use relative cell references (like A2), so they adjust correctly for each row where the formula is copied.

To combine **text from multiple cells** in Excel 365 and Excel 2019, you can leverage the TEXTJOIN function. Its syntax provides for a delimiter (the first argument), which makes the formular more compact and easier to manage.

For example, to add strings from three columns (A, B and C), separating the values with a comma and a space, the formula is:

`=TEXTJOIN(", ", TRUE, A2, B2, C2)`

## How to add special character to cell in Excel

To insert a special character in an Excel cell, you need to know its code in the ASCII system. Once the code is established, supply it to the CHAR function to return a corresponding character. The CHAR function accepts any number from 1 to 255. A list of printable character codes (values from 32 to 255) can be found here.

To add a special character to an existing value or a formula result, you can apply any concatenation method that you like best.

For example, to add the trademark symbol (™) to text in A2, any of the following formulas will work:

`=A2&CHAR(153)`

`=CONCATENATE(A2&CHAR(153))`

`=CONCAT(A2&CHAR(153))`

## How to add text to formula in Excel

To add a certain character or text to a formula result, just concatenate a string with the formula itself.

Let's say, you are using this formula to return the current time:

`=TEXT(NOW(), "h:mm AM/PM")`

To explain to your users what time that is, you can place some text before and/or after the formula.

**Insert text before formula**:

`="Current time: "&TEXT(NOW(), "h:mm AM/PM")`

`=CONCATENATE("Current time: ", TEXT(NOW(), "h:mm AM/PM"))`

`=CONCAT("Current time: ", TEXT(NOW(), "h:mm AM/PM"))`

**Add text after formula:**

`=TEXT(NOW(), "h:mm AM/PM")&" - current time"`

`=CONCATENATE(TEXT(NOW(), "h:mm AM/PM"), " - current time")`

`=CONCAT(TEXT(NOW(), "h:mm AM/PM"), " - current time")`

**Add text to formula on both sides:**

`="It's " &TEXT(NOW(), "h:mm AM/PM")& " here in Gomel"`

`=CONCATENATE("It's ", TEXT(NOW(), "h:mm AM/PM"), " here in Gomel")`

`=CONCAT("It's ", TEXT(NOW(), "h:mm AM/PM"), " here in Gomel")`

## How to insert text after Nth character

To add a certain text or character at a certain position in a cell, you need to split the original string into two parts and place the text in between. Here's how:

- Extract a substring preceding the inserted text with the help of the LEFT function:

LEFT(cell, n) - Extract a substring following the text using the combination of RIGHT and LEN:

RIGHT(cell, LEN(cell) -n) - Concatenate the two substrings and the text/character using an ampersand symbol.

The complete formula takes this form:

*cell*,

*n*) & "

*text*" & RIGHT(

*cell*, LEN(

*cell*) -

*n*)

The same parts can be joined together by using the CONCATENATE or CONCAT function:

*cell*,

*n*), "

*text*", RIGHT(

*cell*, LEN(

*cell*) -

*n*))

The task can also be accomplished by using the REPLACE function:

*cell*,

*n+1*, 0, "

*text*")

The trick is that the *num_chars* argument that defines how many characters to replace is set to 0, so the formula actually inserts *text* at the specified position in a cell without replacing anything. The position (*start_num* argument) is calculated using this expression: n+1. We add 1 to the position of the nth character because the text should be inserted after it.

For example, to insert a hyphen (-) after the 2^{nd} character in A2, the formula in B2 is:

`=LEFT(A2, 2) &"-"& RIGHT(A2, LEN(A2) -2)`

Or

`=CONCATENATE(LEFT(A2, 2), "-", RIGHT(A2, LEN(A2) -2))`

Or

`=REPLACE(A2, 2+1, 0, "-")`

Drag the formula down, and you will have the same character inserted in all the cells:

## How to add text before/after a specific character

To insert certain text before or after a particular character, you need to determine the position of that character in a string. This can be done with the help of the SEARCH function:

*char*",

*cell*)

Once the position is determined, you can add a string exactly at that place by using the approaches discussed in the above example.

### Add text after specific character

To insert some text after a given character, the generic formula is:

*cell*, SEARCH("

*char*",

*cell*)) & "

*text*" & RIGHT(

*cell*, LEN(

*cell*) - SEARCH("

*char*",

*cell*))

Or

*cell*, SEARCH("

*char*",

*cell*)), "

*text*", RIGHT(

*cell*, LEN(

*cell*) - SEARCH("

*char*",

*cell*)))

For instance, to insert the text *(US)* after a hyphen in A2, the formula is:

`=LEFT(A2, SEARCH("-", A2)) &"(US)"& RIGHT(A2, LEN(A2) - SEARCH("-", A2))`

Or

`=CONCATENATE(LEFT(A2, SEARCH("-", A2)), "(US)", RIGHT(A2, LEN(A2) -SEARCH("-", A2)))`

### Insert text before specific character

To add some text before a certain character, the formula is:

*cell*, SEARCH("

*char*",

*cell*) -1) & "

*text*" & RIGHT(

*cell*, LEN(

*cell*) - SEARCH("

*char*",

*cell*) +1)

Or

*cell*, SEARCH("

*char*",

*cell*) - 1), "

*text*", RIGHT(

*cell*, LEN(

*cell*) - SEARCH("

*char*",

*cell*) +1))

As you see, the formulas are very similar to those that insert text after a character. The difference is that we subtract 1 from the result of the first SEARCH to force the LEFT function to leave out the character after which the text is added. To the result of the second SEARCH, we add 1, so that the RIGHT function will fetch that character.

For example, to place the text *(US)* before a hyphen in A2, this is the formula to use:

`=LEFT(A2, SEARCH("-", A2) -1) &"(US)"& RIGHT(A2, LEN(A2) -SEARCH("-", A2) +1)`

Or

`=CONCATENATE(LEFT(A2, SEARCH("-", A2) -1), "(US)", RIGHT(A2, LEN(A2) -SEARCH("-", A2) +1))`

Notes:

- If the original cell contains
**multiple occurrences**of a character, the text will be inserted before/after the first occurrence. - The SEARCH function is
**case-insensitive**and cannot distinguish lowercase and uppercase characters. If you aim to add text before/after a lowercase or uppercase letter, then use the case-sensitive FIND function to locate that letter.

## How to add space between text in Excel cell

In fact, it is just a specific case of the two previous examples.

To add space at the **same position** in all cells, use the formula to insert text after nth character, where *text* is the space character (" ").

For example, to insert a space after the 10^{th} character in cells A2:A7, enter the below formula in B2 and drag it through B7:

`=LEFT(A2, 10) &" "& RIGHT(A2, LEN(A2) -10)`

Or

`=CONCATENATE(LEFT(A2, 10), " ", RIGHT(A2, LEN(A2) -10))`

In all the original cells, the 10^{th} character is a colon (:), so a space is inserted exactly where we need it:

To insert space at a **different position** in each cell, adjust the formula that adds text before/after a specific character.

In the sample table below, a colon (:) is positioned after the project number, which may contain a variable number of characters. As we wish to add a space after the colon, we locate its position using the SEARCH function:

`=LEFT(A2, SEARCH(":", A2)) &" "& RIGHT(A2, LEN(A2)-SEARCH(":", A2))`

Or

`=CONCATENATE(LEFT(A2, SEARCH(":", A2)), " ", RIGHT(A2, LEN(A2)-SEARCH(":", A2)))`

## How to add the same text to existing cells with VBA

If you often need to insert the same text in multiple cells, you can automate the task with VBA.

### Prepend text to beginning

The below macros add text or a specific character to the **beginning** **of all selected cells**. Both codes rely on the same logic: check each cell in the selected range and if the cell is not empty, prepend the specified text. The difference is where the result is placed: the first code makes changes to the original data while the second one places the results in a column to the right of the selected range.

If you have little experience with VBA, this step-by-step guide will walk you through the process: How to insert and run VBA code in Excel.

**Macro 1: adds text to the original cells**

This code inserts the substring "PR-" to the left of an existing text. Before using the code in your worksheet, be sure to replace our sample text with the one you really need.

**Macro 2: places the results in the adjacent column**

Before running this macro, make sure there is **an empty column to the right** of the selected range, otherwise the existing data will be overwritten.

### Append text to end

If you are looking to add a specific string/character to the **end** **of all selected cells**, these codes will help you get the work done quickly.

**Macro 1: appends text to the original cells**

Our sample code inserts the substring "-PR" to the right of an existing text. Naturally, you can change it to whatever text/character you need.

**Macro 2: places the results in another column**

This code places the results in a **neighboring column**. So, before you run it, make certain you have at least one empty column to the right of the selected range, otherwise your existing data will be overwritten.

## Add text or character to multiple cells with Ultimate Suite

In the first part of this tutorial, you've learned a handful of different formulas to add text to Excel cells. Now, let's me show you how to accomplish the task with a few clicks :)

With Ultimate Suite installed in your Excel, here are the steps to follow:

- Select your source data.
- On the
*Ablebits*tab, in the*Text*group, click**Add**. - On the
*Add Text*pane, type the character/text you wish to add to the selected cells, and specify where it should be inserted:- At the beginning
- At the end
- Before specific text/character
- After specific text/character
- After Nth character from the beginning or end

- Click the
*Add Text*button. Done!

As an example, let's insert the string "PR-" after the "-" character in cells A2:A7. For this, we configure the following settings:

A moment later, we get the desired result:

These are the best ways to add characters and text strings in Excel. I thank you for reading and hope to see you on our blog next week!

## Available downloads

Add text to cell in Excel - formula examples (.xlsm file)

Ultimate Suite - trial version (.exe file)

## 78 comments

How to delete last letter and replace a new letter? like: ministru to ministri

Hi! Find the position number of the last letter using LEN function. Then use REPLACE function to replace the last letter. For example:

=REPLACE(A1,LEN(A1),1,"i")

Hi there!

I've got a few spreadsheets that require a name to be inputted e.g. Chris, Graham, Carol etc. However last names need to be included in them now and I'm looking for a way to be able to add each persons last name for the cells that already have their first name in. Also would there be a way to add last names that have 2 names per cell? e.g. Chris/Graham. If not it's no problem!

Thank you!

Hi! Using standard Excel tools or formulas, you cannot add a last name to a cell where first name is already written.

You can create an additional column and combine data from two columns in it. Read more: How to merge two columns in Excel without losing data.

Thanks a lot Alex!

Hi - I have cell A1 that has 0.0664 in it but the cell is formatted as a 'Percentage' so you see 6.64%. I am then trying to use the formula ="If you buy "&A1 but the result is 'If you buy 0.0664' but I really want it to show 'If you buy 6.64%'. How can I do that? Thanks

Hello! If you want to combine text with a number in the format you want, use the TEXT function. Based on this information, the formula could be as follows:

="If you buy "&TEXT(A1,"0.00%")

Perfect. Thank you very much Alexander.

In the Macro 1 to pretend text, an effort to run the macro gives an error at the semi colon sunil after & as "Expected: end of statement".

Please suggest corrective measure.

Hi.. can u help me with how can i insert "A" in all sequenced numbers in excel sheet?

Original Become

1 A1

2 A2

3 A3

Hi! Use the SEQUENCE function to create a sequence of numbers. Read more: SEQUENCE function - create a number series automatically. Combine this sequence with the text using the & operator. For example:

="A"&SEQUENCE(10,1,1,1)

How can I format a row to display the # sign before numbers. Instead of a $ I want #.

Hi! We have a special tutorial that can help to solve your problem: Custom Excel number format.

Format example: "#"##0

Hello,

my client entered time-variable cells as 4-digit values without the colon (e.g., "0600", "1430"; i.e., 6 A.M., 2:30 P.M.) but I need the cells formatted as time (e.g., 06:00, 14:30). I've use insertion formulas to place a colon between the 2nd and 3rd characters, but this results in a 'minutes-like' value (e.g., 0600 becomes 60:00). Formatting the new cells as 'hourly times' fails to resolve. Any advice? Thank you

Hi! To convert text to time, you can extract hours and minutes using the LEFT and RIGHT functions and create a time value using the TIME function.

You can also add the desired characters to the text and use the TIMEVALUE function to convert text to time.

Try these formulas:

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

=TIMEVALUE(REPLACE(A1,3,0,":")&":00")

Thank you, Alexander. Your formulas were helpful.

hi! im trying to add ".pdf" to a column of cells.

I use the script above, so I don't have to use the concatenate formula:

Sub AppendText()

Dim cell As Range

For Each cell In Application.Selection

If cell.Value "" Then cell.Value = cell.Value & ".pdf"

Next

End Sub

but it gave me an error message "compile error: syntax error"

Hi! Unfortunately, we do not provide assistance in VBA.

Hello Sir

I want to populate same text upto a certain rows and then another text and then another. For eg

From A1-A33333-ABC

A33334-66666-DEF

A66667-A100000-GHI

Can you please help

Sorry, I do not fully understand the task.

As it's currently written, it's hard to tell exactly what you're asking.

Hello Sir

What I need is same text to be repeated .

A1: ABC

A2: ABC

A3: ABC

this text to be repeated in all rows upto A33333

Then A33334: Workbook

A33335: Workbook

A33336: Workbook

this text to be repeated in all rows upto A66666

Similarly i need to copy the same text

Hi! You can repeat the text in the column as many times as needed using the SEQUENCE function. If this text consists of 3 characters, extract the first 3 characters from the text string using the LEFT function. Please try the following formula:

=LEFT("ABC"&SEQUENCE(33333,1,0,0),3)

How can I make a formula to put an "h" after first set of numbers and an "m" after second set of numbers in a single cell?

Example: 764h 34m (however the values for each cell will be different.)

I am taking data from one source and entering it into excel. These h and m represent the number of Engine Hours on each piece of machinery in our fleet. They appear just as shown above. Ideally, I would like to be able to type the numbers with a space or . in between them and let excel insert the "h" and "m" so I can just go down the list of data and use my number pad for quick data entry.

Hello! Enter your data in time format. Separate hours and minutes with ":". Read more:

Use a custom time format

[h]"h "mm"m"

For more information, please visit: How to show over 24 hours, 60 minutes, 60 seconds in Excel.

sir how to make for an example 16VM077 to 16-VM-077

Hi! Pay attention to the following paragraph of the article above: How to insert text after Nth character.

It covers your case completely.

Hi,

May I seek your help. what is the possible for this

Given data:

in Cell1A contains : apple, orange, banana

the result I want in Cell1A: 1.) apple 2.) orange 3.) banana

Hi! You can change the text in the cell in which it is written using a VBA macro. You can also do it using Ultimate Suite as described in the article above.

Hi, is it possible to concatenate two cells into separate paragraphs in a third cell? Something I would typically do using Alt+Enter?

For example,

A1: "Hi, "

B1: "Hope you're doing well"

C1 shows:

"Hi,

Hope you're doing well"

Thanks

Hi! If I understand your task correctly, this article may be helpful: CONCATENATE in Excel: combine text strings, cells and columns. For example:

=CONCATENATE(A1,CHAR(10),B1)

I'm trying to auto fill a column with the word "COMPLETED" if duplicate entries occur in two other, separate columns.

I.e. Column A contains a variety of numbers. Column B contains specific numbers that can also be found in Column A. Column C should show the word "COMPLETED" if the number is found in both Columns A & B

Hi! You can find the examples and detailed instructions here: How to compare two columns in Excel for matches and differences.

Hi Team,

I want to replace 3 special character like "*" in the end of sentence

Jimy saputra = jimy sapu***

Asep surasep = Asep sura***

Alexander Trifuntov = Alexander Trifun***

thanks a lot in advance:-

Hi! To replace the last three characters in a text string, use the REPLACE function:

=REPLACE(A1,LEN(A1)-2,3,"***")

Dear Team,

I want to add spaces like the expected results,

thanks a lot in advance:-

C2160feetRoad

C41OPP240B

C284981C12

G14sunderIndustrialstate

Their expected results should be as:-

c2 160 feet Road

c41 OPP 240 B

c2 84981 c12

G14 sunder industrial state

Hi! To make a formula and add spaces, you need to see some kind of regularity or pattern in your data. Unfortunately, I don't see that.

Thanks Sir,

possible to formula to add space ist after two character,then three character,then three character in a single cell please

Hi! To insert a character at a specific position, you can use the REPLACE function. For example, insert space:

=REPLACE(A1,3,0," ")

Thanks Sir,

This is only for ist three character, but i need a single formula to make space in a cell after every two or three spaces

e,g:C2160feetRoad should be as c2 160 feet Road.

Hi! To insert a space after every third character, split the text string with the MID function, then merge it with the TEXTJOIN function using a space as separator.

=TEXTJOIN(" ",TRUE,MID(A1,SEQUENCE(15,1,3,3),3))

Hello,

I'm attempting to extract the text from many cell notes. The form of the text in each cell note is:

ABC

DEF

GHI

I setup the following function:

Function getComment(incell As Range) as String

On Error Resume Next

getComment = incell.Comment.Text

End Function

----------------

When I call the function on a cell, the result is:

ABCDEFGHI

How to insert a delimiter such as 'space' or 'comma' in between each line so it looks like:

ABC,DEF,GHI

or

ABC DEF GHI

Thanks!

Hi

Grateful if you could please help. I have one cell AB and another cell C. How to add C between AB please?

Hi! If I understand your task correctly, try the following formula:

=REPLACE(A1,2,0,B1)

or

=LEFT(A1,1)&B1&RIGHT(A1,1)

where

A1="AB"

B1="C"

You can learn more about REPLACE function in Excel in this article on our blog.

I have data from a mainframe and the date fields do not have a / separator to separate the month/day/year. I can use a formula you provided. When I use =LEFT(E2, 4) &"/"& RIGHT(E2, LEN(E2) -4) this works perfectly to put a separator before the 4-character year, if the date is 8 characters in length. example: 11162002 will be 1116/2002 but if the date is 7 characters in length I get this - 6162002 will be 6162/002. If I move the left and right functions ( =RIGHT(E2, 4) &"/"& LEFT(E2, LEN(E2) -4) ) I get this result 2002/1116. This is very confusing to me. I need to count 4 characters starting from the right and insert a / and then count 7 characters from the right again to insert another / to all dates will look like this. 11/16/2002 for a 8 character date string or 6/16/2002 for a 7 character string but I am unable to find a solution. What would these 2 formulas look like. Please provide 1st formula to separate the year and 2nd formula to separate the month/day.

thank you in advance

Hi!

For your date 6162002 you can use the DATE formula and extract the necessary data using substring functions

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

this works great, I've only tried a few so far. I would love to learn how this whole formula works - can you break this down bit-by-bit?

thanks in advance for your help

For more information on each of the LEFT, RIGHT and MID functions, read this article: Excel substring functions to extract text from cell.

This does not address inserting from the right.

I have a series of digits I wand to insert a decimal into from the right by 2 places. Every guide I find only addresses inserting from the left.

Hi!

Are the recommendations of the paragraph "How to add text to the end of cells in Excel" not suitable for you?

I might have overlooked something, and sorry if I did, but I'm not understanding how I would add a "." into an existing string, 2 places from the right.

562357 > 5623.57

Well I managed somehow.

Still don't see how the section you referred to is relevant to my question.

=RIGHT(REPLACE(M2,LEN(M2)-3+2,LEN(M2),""), 10) & "." & RIGHT(M2,2)

Hi!

You need not add, but insert a symbol. Use the REPLACE function

=REPLACE(A1,LEN(A1)-1,0,".")

I hope it’ll be helpful.

It is not helpful, in order to use this tutorial you have to already be an expert. You need to do the explanation for dummies as well.

Hello. How can I add brackets to all cells. For instance, the cell reads "$Stop1" and I want "{$Stop1}."

Hi!

Pay attention to the second and third paragraphs in the article above.

If the cell I'm drawing in with CONCAT is currency which ends in ".00" or, say, ".10", it truncates the zero so I get $2 or $2.2 without the remaining zeros. How do I force the 2 space decimal?

Hello!

To display the currency in the desired format, use the TEXT function.

For example,

=CONCATENATE("Your price is ",TEXT(A1, "$###,###.00"))

Function SplitWords(Txt As Range) As String

Dim Out$

If Len(Txt) = 0 Then Exit Function

Out = Mid(Txt, 1, 1)

For i = 2 To Len(Txt)

If Mid(Txt, i, 1) Like "[a-z]" And Mid(Txt, i + 1, 1) Like "[A-Z]" Then

Out = Out & Mid(Txt, i, 1) & " "

Else

Out = Out & Mid(Txt, i, 1)

End If

Next i

SplitWords = Out

End Function

HI! I need help.

I want a serial number entered on one sheet to automatically populate within a sentence on another sheet.

For instance:

A user enters the serial number on Sheet 1 in Cell B5 as 1234-Q.

I want Sheet 2 to have a sentence within a cell that says "the service for serial number '1234-Q' is complete". How can I do this? Paste special doesn't exactly work.

Appreciate any help, thanks!

Hello!

The answer to your question can be found in this article: Excel CONCATENATE function and "&" to combine strings and cells.

="the service for serial number "&Sheet1!B5&" is complete"

You can learn more about external references to another sheet in Excel in this article on our blog.

A B

1000403 DRUGS ALLOPURINOL

(ALLOPURINOL USP)

1000448 TRI ETHYL

ALUMINUM

1000465 TRI ETHYL

ALUMINUM

1000552 DRUGS: LOSARTAN

POTASSIUM USP

(LOSARTANPOTASSIUM)

1000916 COMBED

COTTON CORESPUN SEWING THREAD

70%POLYESTER FILAMENT YARN

42%

1001356 BOPP FILMS

BIAXIALLY ORIENTED POLYPROPYLENE FILM

in this database, I want to add texts spread over 2-3-4-n nos of Cells at Cl-B against individual entry(first column) in a single cell. Considering the database is huge and number of cell over which text is spread is not limited, use of concatenate may not be helpful.

Hi!

I don't really understand what you want to do. But for merging cells, you might find this article helpful: Merge and combine cells in Excel without losing data.

If this is not what you wanted, please describe the problem in more detail. Write an example of the result you want to get.

How can I add text to a formula like =SUM(224/12) so the result would be 18.6 days

Hi!

You can add text to a formula using the & operator. Read more in this article: CONCATENATE in Excel: combine text strings, cells and columns.

=SUM(224/12)&" days"

How do I add a comma and a space between characters? My cells return multiple characters in one cell (I want that) and I would like to add a comma and space between each character.

Thank you for your help!!

Elyse

Hello!

What formula do you use to get the characters in a cell? We need to change this formula. To write text with commas in another cell you can use the TEXTJOIN function

=TEXTJOIN(",",TRUE,MID(A1,ROW(A1:A50),1))

I want to add "Address" but can not put as =""Address"" since it does not take it as text. how I can add this ?

Hi!

If Excel does not treat your text as a formula, add an apostrophe ' before the text.

how do you enter name, and letter by letter it goes to each next Cell,

for example : JOHN RAMBO,

J O H N R A M B O in each cell

Hi!

After each letter, press the cursor arrow key to move to the next cell.

i need to copy data from other form and put in an excel sheet, which read each letter for each cell, kind of hard adding list of name to it by using arrow keys, as names from my country had kind of long names...

Hi!

Sorry, it's not quite clear what you are trying to achieve. Please describe your problem in more detail.

okay, the sheet than i had was make by the printing company and some how if edited it will be printed differently, im suppose to fill in the name from a physical form into the sheet, in which read each letter of the name in each cell, the problem was it become a hassle each time for me to enter the name for example :

JOHN DOE

i had to enter the name

J "ARROW KEY" O "ARROW KEY" H "ARROW KEY" N "ARROW KEY" *BLANK* "ARROW KEY" D "ARROW KEY" O "ARROW KEY" E

so in the sheet it will be [ J ][ O ][ H ][ N ][ ][ D ][ O ][ E ]

its okay to type in like this, but if you had 300 names with 21 letter, well... poop..

it will be helpful if you can solve this silly problem of mine... thank you

Hi!

Your problem can be solved with a VBA macro. But now I can only help you with Excel formulas.

Good day.

Please which formula in excel can ad space after 2nd upper case character.. e.g. "JohnDavis" to appear as "John Davis"

Thank you

Hello!

To insert a space before the second uppercase letter, use the formula:

=LEFT(A1,MATCH(TRUE,EXACT(MID(A1,ROW(2:50),1), UPPER(MID(A1,ROW(2:50),1))),0))&" "&MID(A1,MATCH(TRUE,EXACT(MID(A1,ROW(2:50),1), UPPER(MID(A1,ROW(2:50),1))),0)+1,50)

I hope it’ll be helpful.

Hi! can ask if this formula is possible to this

Macaraeg,ChristopherLabanaBautista

thank you

Hello!

To split sticky text and insert spaces into it, you need to use VBA. You can try to apply a user defined function

Function SplitWords(Txt As Range) As String

Dim Out$

If Len(Txt) = 0 Then Exit Function

Out = Mid(Txt, 1, 1)

For i = 2 To Len(Txt)

If Mid(Txt, i, 1) Like "[a-z]" And Mid(Txt, i + 1, 1) Like "[A-Z]" Then

Out = Out & Mid(Txt, i, 1) & " "

Else

Out = Out & Mid(Txt, i, 1)

End If

Next i

SplitWords = Out

End Function

To insert a space after a comma, use the SUBSTITUTE function.

I hope it’ll be helpful. If something is still unclear, please feel free to ask.