function is used to join text or numbers (known as Strings. It can join up to 30 strings although I suppose you could nest two or more CONCATENATE functions inside one another. I have more important things to do such as writing the remainder of this post.
I'll give you 2 examples.
Example 1 (Joining 2 names together)
In range B3:B12 I have a list of first names and in range C3:C12 I have a list of last names. Concatenating makes joining these names together a snap. In cell D3 I enter =CONCATENATE(B3," ",C3)
and drag down to cell D12 where the formula will become =CONCATENATE(B12," ",C12)
Note the apostrophe marks, they are used to include spaces or text. Cell references (such as B3) or numbers do not use them. (I could also ampersands to concatenate as in =B3&" "&C3
but I've found after getting used to spelling CONCATENATE
, it's easier for me to enter several strings into a formula quickly)
Example 2 (Making an email address)
To do this I am going to use a combination of 3 formulas, CONCATENATE
. Let's start with LEFT
It seems to me that many email addresses use the first letter of the first name and then all of the last name combined before the @ mark. To get the first letter of Tom in cell B3 I use =LEFT(B3,1)
. For more letters I can just increase the number after B3, these letters will always be read from the left side, hence the name LEFT
Another simple function, it is used to convert all text to Lower Case
so that TSmith will appear as tsmith. (The UPPER
function is used for Upper Case)
I should say that I don't really need to use this function as either upper and lower case is still okay for email addresses. I just want to make it easier to recognize an email address as opposed to normal text.
Refer to the above. Now lets look at the entire formula,
This will give me email@example.com (you'll need to use Hyperlinks to make them 'clickable' email addresses, use Ctrl and K to bring forth the dialog box)
Now that we have a list of email addresses, let's make a directory using a Combo Box. Right click any toolbar and then tick the Forms toolbar.
Drag the Combo Box to your preferred location and then right click it to select the Format Control dialog box.
Your Input Range will be D3:D12. Push the little arrow button on the right side and drag across these cells, the $ marks will appear automatically to make this an 'absolute' reference (look at the Help files to understand if you are not sure what this means)
Next up is your Cell Link. I usually hide these under my Combo Boxes so they don't stand out. You can see that I am using cell D14.
Finally we have Drop Down Lines, I've increased to 10 from a default number of 8 so that my entire list of email addresses can be seen.
When you use the Combo Box, the Cell Link will change. If you select the fourth name from the top (Jane Williams), the value in cell D14 will become 4 (1 for first, 2 for second, 3 for third, etc)
All I need is some way to reference the actual addresses. I've chosen the INDEX function to do this.
The range F3:F12 contains the email addresses, D14 is the Cell Link that will give me the vertical (row) reference as above. (A horizontal reference is not necessary as we are just dealing with a single column)
Simple once you know how.
Posted by andrewe at 16:45
Your concatenate explanation is help, but could you explain one thing further please;
I have a column of last names, than a column of first's, I want to combine as follows:
could you tell me the formula for this, I cannot seem to get a period in there between the names, etc
Any advice appreciated,
Try this (remove quotation marks when dealing with cell references)
I have a row of multiple keywords that I would like to combine into one cell, seperated by a comma. For example:
I would like one cell to show:
audio, visual, USB, firewire, IEEE
I've played with concatenate, but it seems like it will not do more than two rows.
Ultimately, I'd like to create a macro to auto-merge each list of keywords.
Please help! If you need further information, please email me.
There's a VBA function I know called "Chain" that might help.
Here's the link.
Conrado Meria Antolin,
Lorraine Fumie Asahara,
The Rev. Mabel Kaululaau Bajet,
Elizabeth W. Bal,
I want to place the last name first, then the rest of the name behind it.
If A1 is where the first name is located, then in B1 use =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))), then in C1 use =SUBSTITUTE(A1,B1,"").
This should do the trick ;-)
How can i join text from different cells and keep the same font formats? e.g.
A1: Hello (in red font color, bold)
A2: World (in black font color, normal)
A3: =A1 & A2 (keeping the same font format for each word)
Enter this code in a standard module
It will work in any cell (multiple cells are okay)
On Error Resume Next
' exit if not range
If TypeName(Selection) <> "Range" Then Exit Sub
Dim c As Range
' (Join this code to the top part)
For Each c In Selection
' ignore cells in Columns A & B
If c.Column = 1 Or c.Column = 2 Then GoTo StartOver
' ignore cells with no values
If Cells(c.Row, 1) = "" Or Cells(c.Row, 2) = "" Then GoTo StartOver
' cell value is Columns A & B with a space between
c = Cells(c.Row, 1) & " " & Cells(c.Row, 2)
' revert formatting just in case
.ColorIndex = 0
.Bold = False
' add bold / red font for values in Column A
With c.Characters(Start:=1, Length:=Len(Cells(c.Row, 1))).Font
.FontStyle = "Bold"
.ColorIndex = 3
Please help me with the concatenate formula.I was shown once how to do this in excel at one time years ago but forgot how.
A. Change cell numbers 024-747050-503 to numbers 024747050503
B. Drop off left 4 digits. From 024747050503 to number 47050503
C. Add left 4 digits. From 47050503 to number 024747050503
Any help with this would be deeply appreciated.
Don't know if you want these in different cells or not,
024-747050-503 in A1
=SUBSTITUTE(A1,"-","") in A2
=MID(A2,5,255) in A3
What is the difference between A and C? Are the 4 digits added always the same as the ones removed?
This formula will revove the dashes and first 4 digits.
A= remove dashes
C= add 0247 in front of 47050503
These functions work great.
In your example:
=MID(A2,5,255) I tried replacing 255 with other numbers like 719 and it functioned the same. Not sure what the 255 represents.
I remember using =CONCATENATE() with RIGHT MID LEFT in the formula.
Is this another way to do the same
There are many ways to do the same thing. All part of the fun.
255 is the maximum number of characters in a cell (current Excel versions anyway). Things will change with Excel 12, not yet released.
Is there any way to make them all clickable automatically?
You can do it with VBA. Enter this in a standard module.
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
On Error Resume Next
ShellExecute 0, vbNullString, "mailto:" & ThisWorkbook.Sheets(1).Range("F14").Text, vbNullString, vbNullString, vbMaximizedFocus
Then in Excel, insert a button from the Form toolbar and assign the above "EmailFromCell" macro. Change the button text etc to suit.
I was hoping to get some help on concatenate functions. I am attempting to use a nested concatenate function in a sumif function. However instead of only pulling out one concatenated value I want it to pull out values of 2 concatenated values at the same time. The only way I can do it at the moment is to do them seperatly and add them together. The probem with this is that my formula gets too big to fit in the formula box. Can anyone help me with this problem.
Anthony, this is a one-person blog so there's just me ;-)
If you've got a doozy, try our JMT Forum if you want.
These comments are limited to 1,000 characters, hence me replying with more than comment where necessary. Sounds like you want some extra space though :-)
2 questions for you:
I have two columns of data (both large lists) that I want merged together. Each column has 2500+ cells. The concatenate function has a limit of 30 characters. How do I do this?
If I have two worksheets, ws1 has a single column of data while ws2 has 20. How can I search for the value of ws1 A2 in ws2 and have excel display all 20 other cells of the row?
many MANY thanks!
#1 You are going to hit a wall with a maximum of 255 characters in a cell so you'll need to use more than one cell. As for joining them, try the Chain function from my Custom Functions in the Downloads list (link at top).
#2 Try searching for each one and use & to join them? This may require more than cell too. Sounds like you have a lot of data.
I have a column of cells containing numbers with zeros in front of them.They range from 10 to 20 characters long.I need to keep all numbers just like they are without losing the zeros.Is there one formula to use for entire list.
I also have a list of numbers as above with zeros and dashes and I need to keep thes just like they are withoutlosing any characters.
If you are just joining them, use the Chain function as mentioned above.
I am not joining these numbers.These are part numbers with different character lengths starting with zeros and some have dashes.I have thousands of cells to cut and paste.I can not lose the zeros.What should I do.
I hae used the concatenate command pretty regularly. I am trying to use it on a preexisting sheet, but I keep getting a "#Value!" error. I have tried changing the formats of all of the cells, but it still doesn't work. Any ideas?
Not sure what the cause may be, could be a missing value or another error.
If you want to hide the errors, have a look at these links.
I want to create a database for a reunion. I have: year of graduation, last name, first name, street address, city, state, zip, email, etc. all in separate columns. I know how to Concatenate to bring the individual columns into one cell, but is there any way that I can use Concatenate to make the individual cell look like a mailing label? ie. Name, Year
City, State, zip
Use CHAR(10) for line breaks as in ="Name"&CHAR(10)&"Address" or =CONCATENATE("Name",CHAR(10),"Address"), then right-click the cells and choose Format, Alignment. You'll see 3 checkboxes on the left, tick the top one.
I have a problem with the text limitation of 256 characters.
In one workbook I have a cell expanding with status information over time. It displays > 255 characters, no problems.
In another workbook I have a link to the first workbooks status cell. The cell containing the link only displays the first 255 characters of the referenced cells contents.
Can you see a way around this problem. Priority must be to keep the original status reporting easy because it is distributed to a lot of persons.
Thank you in advance
The only idea I can come up with using Excel itself is using multiple cells. Unfortunately I don't work with such large amounts of data so my experience is rather limited. I'd recommend you try a forum, I'm sure others have a solution that may help.
I have a column with a large amount of data and cell format is currently BA-07B for a part location but I need to change the format to be the following B.A.07.B
Is there an easy way to do this?
Thanks in advance
Do you mean BA-07B is a custom format? ("BA-"00"B" ?)
Then you can use "B.A."00".B" or similar...
Is this what you are after?
hi pple,,,i have bigggg problem with excel i need help in excel,,so i take some zero in my exams so ,,if someone can what i feel about my problem in excel....need help,,,,,,,,,,bato lebanon.......bye
Plenty of people to help at our forum.
Join up and check us out :-)
The formula that you gave for email does not appear to work for me. The first letter is not parsed out of the first name. Even if I use just that part of the formula alone nothing happens. Any ideas why this might be the case? Is the Left call not working in some versions of Excel?
Is the cell reference correct? Is there a space or something before what seems to be the first character? These are possible reasons.
I came across your site because I was looking for an answer to my Excel concatenate problem, I hope you can help me. In the formula: ="last"&CHAR(10)&"first", it appears as last, a square box, and first. I really wanted it to be two lines Last and First. I have two columns labelled last and first that contain names and so I am trying to transpose the two columns to 1 row with 2 lines.
Sorry about my wordiness. So basically, I want to make sure my CHAR(10) is a line break and not a square.
Thanks in advance for any guidance.
Right click the cell, go to Format Cells, Alignment, then tick "Wrap Text" and push Okay. Then double click between the row tabs to autofit the row height to suit. (eg the line between row numbers 1 and 2 to the left of Excel)
i'm trying to get excel to return the last two numerical digits of a string of characters. usually the characters are just numbers, like 123456789. in that case, i can use a concatenate formula, but sometimes the characters end with two letters, like 1234567AB. is there a formula i can use to always return the last two numerical characters - to return 89 for 123456789 and 67 for 1234567AB?
thanks in advance for your help!
Try either of these formulas, replace A1 as necessary.
If the first one causes any problems, go for the second one.
can I replace _ in a string with + sign. + only in red color
I have names in three different cells (column a rows 1-3)
cell 1: Mr. & Mrs. Joe Smith
cell 2: 2200 W. Indiana Ave
cell 3: Chicago, IL 60600
Is there any way that I can divide up the information to individual cells, for example Mr & Mrs. into a cell 1; Joe into cell 2; Smith - cell 3; 2200 W. Indiana Ace - cell 4; Chicago - cell 5; Il - cell6; 60600 - cell 7 where the cells are in the same row, but are in columns a-g? Almost like a reverse concatinate? I am trying to save myself hours of copying and pasting! Thanks in advance!
Using Excell 2000, when I enter:
using either the formula generator or manual entry, the result is simply the formula rather than the joined contents of the two cells with an intervening space.
When looking at the formula generating window it says that the result of the formula is "Smith Jr", etc., but when I hit "Enter" to accept I simply see the formula. Pressing F9 doesn't help.
Yes, but you will need VBA for the color. Otherwise try =SUBSTITUTE(A1,"_","+")
Hi Marta, it is possible with a combination of text formulas...but you will need to define what kind of input will exist (always married couples, always something like W. etc) It's not an easy job otherwise to parse data, ...the less variations, the better.
Could be the format. Right click, select Format Cells, Number and make sure the format is not Text.
Is there a way to concat content from HTML files into your formula?
Probably, but I've never tried it. Sorry.
I have a column of data ending in either PM or MM. I've used the =RIGHT(A1,2) function to return the PM or MM, but how do I return the left hand of the data? I can't use =LEFT as the number of characters varies.
I figure you are not referring to the far left characters as LEFT should work fine in this case. For the 3rd and 4th last most characters try =MID(A1,LEN(A1)-3,2). You might want to think about strings that have less than 4 charcters though in a case like this though, the above formula would result in an error.
needed to know how to bold part of the text string joint by concatenate function
You will need VBA. I can't help unless I know some more specifics of the text to be concatenated.
This web log is fabulousand incredibly useful. Following previous comments I can now merge my cells, wrap text and lay out like an address. However I still can't work out how to hold a bold text or how to keep the date function holding the date patern (it reverts to five numbers instead of 27 March 1997) The holding cell is formatted for dates but like the bold this doesn't carry across. I read one of your replys were you refered to putting in code in a module. I'm using Excel2000 and am not sure what this is. Sorry, self taught. If you can help, thanks, if not you've been great so far. Cheers!
I really need to see your file(s) or an example to help in this case.
There are 3 options for you,
1. You can try our free forum
2. I can help personally, send me an email with a file (see the link at the top of my site)
I this case I ask for a small donation for my hopefully soon-to-be family.
But I'll let you know the password for my utilies as a freebie.
3. If it is a serious job, you can try our consulting company.
This will cost more, but we are very serious about doing as professional job as possible.
Keep in mind, a little outlay may make things a lot more productive.
In any case, keep on smiling ;-)
Hi Andrew. Thanks for this awesome blog!
I have this problem: I am pulling out a list of Ministry names and populating it into a combo box. Some Ministry names contain commas in their name, eg: Ministry of Family, Children, and Community Services.
However, this ministry name won't be shown in one row, it will be split up after each comma. How would I resolve this issue?
Thanks a lot
My pleasure Yoga.
This is hard to answer because anything I recommend will need to know how many words and how many commas there are if I use normal functions. But something should be possible by looping through the names with VBA.
In that case, please refer to the options I gave Liz. If you pick number 2, I might take a while as I am a little busy just now with another project, best to go with No 1 or 3.
Best of luck in any case.