May 30, 2004

Concatenate Concentrate

The CONCATENATE 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, LOWER and LEFT. Let's start with LEFT.

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

LOWER
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.

CONCATENATE
Refer to the above. Now lets look at the entire formula,

=LOWER(CONCATENATE(LEFT(B4,1),C4,"@andrewsexceltips.com"))

This will give me tsmith@andrewsexceltips.com (you'll need to use Hyperlinks to make them 'clickable' email addresses, use Ctrl and K to bring forth the dialog box)

Referencing
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.

=INDEX(F3:F12,D14)

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.

Write a Comment
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:

first.last@company.com

could you tell me the formula for this, I cannot seem to get a period in there between the names, etc

Any advice appreciated,

Evan Lowe
Tokyo
Posted by Evan Lowe at February 10, 2005 13:12
Hi Evan,

Try this (remove quotation marks when dealing with cell references)

=CONCATENATE("first",".","last","@company.com")

Good luck.
Posted by Andrew at February 10, 2005 14:09
I have a row of multiple keywords that I would like to combine into one cell, seperated by a comma. For example:

audio
visual
USB
firewire
IEEE

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.

-Joe
Posted by Joe at February 26, 2005 11:49
Hi Joe,

There's a VBA function I know called "Chain" that might help.

Here's the link.

http://www.andrewsexceltips.com/Andrews_Custom_Functions.zip

Andrew
Posted by Andrew at February 26, 2005 23:39

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.
Posted by Ken Linnenburger at March 10, 2005 08:09
Hi Ken,

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 ;-)

Andrew
Posted by Andrew at March 10, 2005 12:00
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)

thnx
Posted by Besho at November 16, 2005 04:03
Enter this code in a standard module

It will work in any cell (multiple cells are okay)

Sub JoinCellsWithRedBoldFormatForFirstWord()
On Error Resume Next
' exit if not range
If TypeName(Selection) <> "Range" Then Exit Sub
Dim c As Range

continued below
Posted by Andrew at November 16, 2005 11:38
' (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
With c.Font
.ColorIndex = 0
.Bold = False
End With
' 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
End With
StartOver:
Next
End Sub
Posted by Andrew at November 16, 2005 11:39
Andrew,
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.
Thanks,Craig
Posted by Craig Price at December 11, 2005 13:24
Hi Craig,

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.

=MID(SUBSTITUTE(A1,"-",""),5,255)
Posted by Andrew at December 11, 2005 13:40
Andrew,
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.

Also,
I remember using =CONCATENATE() with RIGHT MID LEFT in the formula.
Is this another way to do the same

Craig


Posted by Craig Price at December 11, 2005 14:52
Hi Craig,

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.
Posted by Andrew at December 11, 2005 15:08
Is there any way to make them all clickable automatically?
Posted by Chris at December 14, 2005 08:15
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

Sub EmailFromCell()
On Error Resume Next
ShellExecute 0, vbNullString, "mailto:" & ThisWorkbook.Sheets(1).Range("F14").Text, vbNullString, vbNullString, vbMaximizedFocus
End Sub

Then in Excel, insert a button from the Form toolbar and assign the above "EmailFromCell" macro. Change the button text etc to suit.
Posted by Andrew at December 14, 2005 13:19
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 UK
Posted by Anthony Armstrong at December 28, 2005 21:33
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.

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl

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 :-)
Posted by Andrew at December 28, 2005 21:55
Andrew,
2 questions for you:

#1:
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?

#2:
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!
~J

Posted by Jason Johnson at December 30, 2005 07:12
Hi Jason,

#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.

Posted by Andrew at December 30, 2005 14:12
Andrew,
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.
Example:
0001234567
0012345678901
012345678912345
00012345678912345678
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.
Example:
000-1234-57
0000-123455-123
00000-12345678-01234
Thanks Craig
Posted by Craig Price at January 09, 2006 07:57
Hi Craig,

If you are just joining them, use the Chain function as mentioned above.
Posted by Andrew at January 09, 2006 10:40
Andrew,
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.
Thanks Craig
Posted by Craig Price at January 09, 2006 12:16
Andrew,
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?
Thanks,
Travis
Posted by Travis Naugle at January 18, 2006 15:02
Hi Travis,

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.

http://www.andrewsexceltips.com/menu_formulas_t&n_ignore_errors.htm

http://www.andrewsexceltips.com/menu_formats_hide_errors.htm
Posted by Andrew at January 18, 2006 16:37
Andrew,

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
Street Address
City, State, zip

Many Thanks!!
Marta
Posted by Marta at January 19, 2006 01:18
Hi Marta,

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.
Posted by Andrew at January 19, 2006 12:59
Hi

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
Gote Bernhem
Posted by Gote Bernhem at January 27, 2006 17:55
Hi Gote,

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.
Posted by Andrew at January 31, 2006 19:03
Hi Andrew,

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
Jaime
Posted by Jaime at February 01, 2006 06:15
Hi Jaime

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?
Posted by Andrew at February 01, 2006 12:21
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
Posted by bato at February 05, 2006 02:51
Hi Bato,

Plenty of people to help at our forum.

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl

Join up and check us out :-)
Posted by Andrew at February 07, 2006 15:52
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?
Posted by Paul Guba at February 28, 2006 22:48
Is the cell reference correct? Is there a space or something before what seems to be the first character? These are possible reasons.
Posted by Andrew at February 28, 2006 23:34
Andrew,

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.

Lee
Posted by Lee at March 01, 2006 16:01
Hi Lee,

Try this,

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)
Posted by Andrew at March 01, 2006 23:57
hi andrew!

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!
jennifer
Posted by jennifer at March 17, 2006 06:39
Hi jennifer

Try either of these formulas, replace A1 as necessary.

=IF(ISERR(LEFT(RIGHT(A1,2),1)+RIGHT(A1,1)),LEFT(RIGHT(A1,4),1)&LEFT(RIGHT(A1,3),1),LEFT(RIGHT(A1,2),1)&RIGHT(A1,1))

or

=IF(ISERR(LEFT(RIGHT(A1,2),1)+RIGHT(A1,1)),VALUE(LEFT(RIGHT(A1,4),1)&LEFT(RIGHT(A1,3),1)),VALUE(LEFT(RIGHT(A1,2),1)&RIGHT(A1,1)))

If the first one causes any problems, go for the second one.
Posted by Andrew at March 17, 2006 13:16
can I replace _ in a string with + sign. + only in red color
Posted by Ganil Kumar at March 17, 2006 13:30
Hi Andrew,
Another question:
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!
Marta
Posted by marta at March 19, 2006 01:55
Using Excell 2000, when I enter:

=CONCATENATE(B8," ",F8)

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.

Any ideas?

Thanks
Posted by John Fakan at March 19, 2006 07:58
Ganil,

Yes, but you will need VBA for the color. Otherwise try =SUBSTITUTE(A1,"_","+")
Posted by Andrew at March 19, 2006 21:39
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.
Posted by Andrew at March 19, 2006 21:45
Hi John,

Could be the format. Right click, select Format Cells, Number and make sure the format is not Text.
Posted by Andrew at March 19, 2006 21:47
Is there a way to concat content from HTML files into your formula?
Posted by Tim at March 25, 2006 02:21
Probably, but I've never tried it. Sorry.
Posted by Andrew at March 25, 2006 13:39
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.
Posted by Tony at April 07, 2006 23:49
Hi Tony,

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.
Posted by Andrew at April 09, 2006 22:52
needed to know how to bold part of the text string joint by concatenate function
Posted by hortn at April 12, 2006 09:02
You will need VBA. I can't help unless I know some more specifics of the text to be concatenated.
Posted by Andrew at April 12, 2006 11:50
Hello Andrew
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!
Posted by Liz hurley at April 21, 2006 01:42
Hi Liz,

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

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl

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.

http://jmt.puremis.net/index.htm

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 ;-)
Posted by Andrew at April 21, 2006 09:27
Hi Andrew. Thanks for this awesome blog!

MSACCESS:
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
Yoga
Posted by Yogananthar Ananthapavan at April 21, 2006 22:36
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.
Posted by Andrew at April 22, 2006 18:19