Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Concatenate Function- Two Texts In Two Cells

I have two texts in two cells. e.g in A1 I have JOHN, and in B1 I have SMITH.

I need to cocatenate these two texts in a third cell (=CONCATENATE(A1, B1) /or I can use = A1 & " " & B1). It's fine till I concatenate.

But I need the output in the following format:


The second text needs to be in ITALICS.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Using Function To Concatenate Cells
I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).

View Replies!   View Related
Concatenate Function..
What function can get me the first letter from the first name and the whole last name together and then add Example: Hanry Jones =

View Replies!   View Related
CONCATENATE Function (dropping The 0 From B2)
I am using the function =CONCATENATE(A2,B2,C2)
Why is it dropping the 0 from B2?
Table 1 Table 2 Table 3
C 012 A

View Replies!   View Related
Concatenate TODAY() Function
I want a report header to be similar to: REPORT AS OF 2/23/10. If I use the TODAY() function by itself, I get the date; 2/23/10. But when I concatenate it with the text "REPORT AS OF " & TODAY()" I get REPORT AS OF 40232". How do I preserve the date format when I concatenate it with text?

View Replies!   View Related
Vlookup But Do Not Want To Use Concatenate Function
Trying to do Vlookup with If but dont know how , I can do the concatenate function and then Vlookup as in Sheet 3 but that is too much of load to deliver in quick time specially i have pull data in dynamic and continuous update.

View Replies!   View Related
Function Inside Concatenate Funcion
how i get this to work?

B4 = BMEF3


and this its what should do:

how im supposed to do that function work?

View Replies!   View Related
Concatenate Function To Merge Certain Columns
I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.

View Replies!   View Related
Carriage Return In A CONCATENATE Function
I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER). How can I achieve this using =CONCATENATE(......;......;......;......;......)?

View Replies!   View Related
Sum Value From Texts
I have the following in cells a1,a2,a3 & a4 as follows:

A = 14
B = 15
C = 16
D = 20
Total = 65

I need to sum the total of the above, a,b,c & d and show the total. How do I add only the figures and get the total?

View Replies!   View Related
Concatenate Function ... Same Spread Sheet Different Tabs
I want to concatenate two columns in a separate tab but when I do, the values appear as ....

View Replies!   View Related
Concatenate Function To Cell Macro Code
Need to take column J20:J255 and column K20:K255 and concatenate into activesheet K20:K255. This needs to happen when OptionButton1.Value=True. The information in each cell will be different. The following code works well, but it will not allow me to put a space in between the two strings.

Private Sub OptionButton2_Click()
Dim DescriptionCell As Range
Set DescriptionCell = ActiveSheet.Range("D20:D54")
If OptionButton2.Value = True Then
With DescriptionCell
.NumberFormat = General
.Formula = "=CONCATENATE('Bill of Materials-3'!F20,'Bill of Materials-3'!I20)"
End With
End If
End Sub

View Replies!   View Related
Substituting Texts From Specified List
Got the following data in different cells:

And on a different sheet in the same workbook, have 45 different values to be converted:

What is required?

Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.

Basically, for the 1st Cell for eg..


I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.

Result shall look like below:


Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:

It becomes difficult for me. What i need exactly is:

Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.

View Replies!   View Related
Sum Of Numbers Between Texts
I've a cell that contains numbers and texts and I want to sum the numbers and get a result in another cell.

This is an example for my cell:

arando-3,12; hegymeg-4,3; vas-7; aranyoska-1,8

and the result: 16,22

View Replies!   View Related
CONCATENATE Function To Merge 2 Strings Together Into A Single Cell
I have been using the CONCATENATE function to merge 2 strings together into a single cell. However the problem is, the third concatenated cell depends on the other 2 existing. The idea is I want to take 2 columns of data, and use a formula to merge them, then delete the original 2 data columns so I have a single column with the full data merged.

COL 1 (A3) = First Name
COL 2 (B3) = Last Name
COL 3 (C3) = Full Name (wanted)

So I do =CONCATENATE(A3,B3) and that puts the fullname in C3, but I want to sort of "flatten" C3 so that I have the fullname by itself and I can delete the original cells (A, and B)

View Replies!   View Related
Use Input Box Result As Part Of A CONCATENATE Function
VBA code to use input box result in a Concatenate function.

Sub test()
Dim DistName
DistName = Application.InputBox("Enter District Name")
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C[1],"" Diabetes Dist"")"
End Sub

I want to replace R[-2]C[1] with the result from the input box.

View Replies!   View Related
Create A Formula Where Using Different Texts To Display A Value
I would like to create a formula that allows the destination cell display a number depending on a word that is typed in the source cell. The source cell and the destinating cell will remain the same two cells.


If i type WORD1 in A1 (source cell) then A2 (destination cell) will display 1.5, but how do i make this formula work for a multiple of different words.

example:A2=IF(A1="WORD1",1.5,0 IF(A1="WORD2",3.0,0 IF(A1="WORD3",2.5,0)))

this formula doesn't work, but is basically what i am looking for the formula to do - WORD1 displays 1.5, WORD2 displays 3.0, WORD3 displays 2.5 etc where the source cell and the destinating cell remain the same two cells, it is just the word and its value that change.

View Replies!   View Related
Concatenate Function It Appears To Put Quotes Around The Entire Result

The above statement works great, but when I try to replace the HQA01 (worksheet name) with a cell reference it doesn't work anymore. I want to point to the cell that has that name of the sheet in it rather than hardcode each sheet name.


When I try the &, or the concatenate function it appears to put quotes around the entire result

View Replies!   View Related
Concatenate Function To Join Some Text Strings To A Cell On A Different Sheet
I have been trying to use the concatenate function to join some text strings to a cell on a different sheet - From an input sheet -Sheet 1 named Input, to a Notification Form (Sheet 2). Although the Function Argument display tells me that it will display the result I want it actually displays just the formula. It's a very simple thing

Address 1 10 Downing Street
Address 2 Westminster
Address 3 London
Postcode SW1A 1AA

I want displayed as: 10 Downing Street, Westminster, London, SW1A 1AA in a single merged cell. All I am getting on the Form is =concatenate(Input!c25," ",Input!c26," "Input!c27," "Input!c28). I feel sure that it is a very easy solution but I can't arrive at it!!

View Replies!   View Related
How To Split Texts Into Words (collecting Word And Compounds)
i have wordlists which i would like to enhance through texts. For that purpose i need a macro which reads a text and splitts it into words and put the words into three columns. Lets assume the column where the words are supposed to be put is columns A,C and E. The column A should contain single words containing no spaces from Text, column C should contain compound words which have one space between them and in column E those compounds which have two spaces. Now i will put my existing wordlist in column G before i start splitting texts. The macro should put only those words from text into column A,C and E which dont already exist in columns A,B,C,D,E,F and G. In Column B i will place the corrected or checked words from column A. In column D i will type the corrections and checked versions of those compunds existing in column C. And the column F will contain the compound words which are corrected and checked from Column E.

Column A--> single words splitted from text through macro (containing no spaces)
Column B--> corrected and checked versions of words from column A through user
Column C--> compound words containing one space splitted from text through macro (for example:"etwas mchten" or "do something")
Column D-->corrected version of C through user
Column E-->compound words containing two space splitted from text through macro (for example:"sich uerberzeugen lassen" or "down in dumps")
Column F-->corrected E
Column G--> existing dictionary wordlist through user

Signes like ".", ":",";", "/" or quotation signe itself should be seen as seperator. And the words or compounds splitted from the text should not contain these signs.

The text will be put in another sheet in the same excel file. Lets call that sheet "text". The macro should search for words through whole sheet ignoring empty cells or columns.

None of the columns should be filtered or ordered. The columns shound not be deleted as well. That way the user wont have to correct the same word each time cus the corrected as well as the original words will be there.

A thread with similar purpose was made sent by AdamDay as well, where rylo has posted a good solution already.

View Replies!   View Related
Texts Automatically Change The Currency Depending On The Choice Made In The Previous Userform
I have options buttons in a userform, first is "$" the other is ""

When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.

The formula below was is example

=""&Data!$A$2&" / m"

=""&Data!$A$2&" / kWh"

View Replies!   View Related
Concatenate Cells With % In Them
I have 7 consecutive cells which contain a %
e.g. A1 120%, B1 35% etc

I am trying to put them into one cell using concatenate
but am getting 1.20.3529411764705880.705882352941177

I would also like to separate them by comma,

View Replies!   View Related
Concatenate (two Different Cells Into One)
I'm having a rather difficult time getting this one.

I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.

A picture is worth a thousand..

View Replies!   View Related
Concatenate Multiple Cells
I have a macro to concatenate multiple cells but unfortunately it is not giving the result,

It has to concatenate below mentioned cells and put the result in column N

Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"
ActiveCell.Offset(1, 0).Select
End Sub

View Replies!   View Related
I have the following formula in cell N4:

=CONCATENATE(O4,CHAR(10),"EBC Briefing: "&TEXT(P4,"dd-mmm-yy"),CHAR(10),"Site: ",Q4)

As long as 04:Q4 are ALL non-blank cells, the formula works. However, if ANY of the cells are blank, it displays the green rectangle in N4, but it doesn't offer the Trace option.

View Replies!   View Related
I've been trying to use IF OR and CONCATENATE to read 4 cells and give me a list of parts needed based on the data loaded in the 4 cells. I attached an example showing.

View Replies!   View Related
Format: Concatenate Value Of The Several Cells At One
I concatenate value of the several cells at one. (A1&"/"B1&"/"&C1 and etc. The length of characters may be different and I want it be looking neat by adding spaces. Something like that:

400/ 25/ 30

View Replies!   View Related
Concatenate Cells With Text
I want agglutinate some text values in the same cell. Something like that:


B1=A1+A2+A3 =(John Elton Mark)
B2= A1 +A4 +A2 + A4 +A3 = (John AND Elton AND Mark)

View Replies!   View Related
Concatenate Cells Using Delimiter
I receive multiple worksheets containing transaction data with multiple numbers of row and columns. The data columns contain varying types of information like: price, quantity, location1, location2, loading dock, transaction date, buy/sell, purchaser, seller, currency unit, etc.

I have a need to concatenate into a single cell in a blank column, data from a number of cells in each row -- sometimes two cells, sometimes more. In some instances, I'd like to concatenate data for location1 and location2 with a delimiter like a semicolon-and-a-space to create a cell with: "location1; location2" Or in another instance, I'd like to concatenate multiple cells in a row, like: "location1; price; quantity; transaction date", etc.

I'm looking to create a macro that, with the cursor in a blank cell in a blank row, would open an input box, ask a user to select with the cursor the cells in the row to concatenate and then concatenate them into the blank cell in the row.

Ideally, the macro would populate the entire blank column with the concatenated data for the group of selected cells (from the same columns as in the row where the cells to be concatenated were selected) for each row where there is data. It is also possible that some cells in some rows of the selected columns would contain no data. In that case, I'd be looking to return just the delimiter, like: "location1; ; quantity; transaction date"

Right now, with no macro, I do it by hand like this:

To concatenate into cell I6, the contents of cells A6, B6, G6 and H6, I do a "=CONCATENATE(A6,"; ",B6,"; ",G6,"; ",H6)" in blank cell I6, but I'm looking for a way to do it fast in a macro that would fill in all of Column I.

View Replies!   View Related
Concatenate Several Cells If Not Blank
I am attempting to concatentate several cells (10 to be exact). I only want to include cells that are not blank in the output. I searched the topics and found a similar problem. I attempted to modify that solution to fit my needs but I obviously do not quite understand exactly how it worked. I have attached a sample. The expected results are that if in row 1
columns a through j have any value they will then be concatenated or joined in some other fashiion seperated by a comma. I only want to include non blank values. I have placed the code that I attemted to use in this post.

=IF( COUNTBLANK(GL41:GU44)=0,GL41&", "&GM41&", "&GN41&", "&GO41&", "&GP41&", "&GQ41&", "&GR41&", "&GS41&", "&GT41&", "&GU41,IF(ISBLANK(GU41),""&GL41,GL41&", "&GU41))

Some of these cells are merged as indicated in the range of countblank().

View Replies!   View Related
Concatenate In Each Bank Cells, The Cells Above
I am searching for VB code to concatenate various length, and spaced rows of text, putting the end result in the blank cell above each group. For example (assume excel rows are as below):

concatenate a2 thru a5
concatenate a8 thru a9

View Replies!   View Related
Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.


I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC

View Replies!   View Related
Concatenate Non-adjacent Cells Without Duplicates
I've been working on a spreadsheet that is used to track the application process of numerous clients. In doing this, I have a row at the top of each client that is used as a sumarry column. Below each cell in the top row, I have up to 30 non-adjacent cells that I'm trying to concatenate in the top cell separated by new lines. I'm also wanting to not display any duplicates, as there are up to only 5 steps that could be shown for each record. I found a UDF that will search an array and return only the unique values, but it accepts the data as an array, and it returns it as an array also. I seem to be having trouble passing the array from the sheet as well as formatting and returning the array with line breaks.

In the worksheet cell: ....

View Replies!   View Related
Concatenate Cells Based On Values
I have a column say column A. It has a pattern of cells where there is a code C10A3(alpha numeric) in say A1 and then in cells A2-A9 there are descriptions (alpha only). This continues uniformally down to cell 300. So every x amt of cells down a code appears and then below this for x cells is a description. What i would like to do is create a macro to concatenate the code waith each of the descriptions and paste it in the adjacent cell to the description. So....

A1 =C10A3
A2 =Global
A3 =America
A10 =D05a9
A11 =Global

I would like to concatenate A1 and A2 and have the output in B2 (C10A3Global.)
And A1 and A3 with the output in B3 etc. This should continues until A10 (where the cell contains alpha numeric characters) where nothing is entered in B10 and then in B11 there should be D05A9Global....
The only difference between the description and the cell is numeric characters...

View Replies!   View Related
CONCATENATE Cells Formated As Fractions
I want to concatenate several cells, some of which are formated as fractions,
and have it display those numbers as a fraction. When I perform the
function, it displays the cell in its decimal form.

=CONCATENATE(E2," ( ",F2," in. "," ",G2," ",H2," in. ", I2," ",J2," in."," )
"," color #")

Where: F2, H2 and J2 are fractions.

Result: 12" Stretcher ( 11.625 in. x 23.625 in. x 3.625 in. ) color #

View Replies!   View Related
Concatenate Cells To Create Formula
way of joinng the contents of cells to create a formula?

path month subdept
x:path oct [dept.xls]sheet1!a1
x:path dec [dept.xls]sheet1!a1

is there any way of joining these to create a cell reference such as x:path oct[dept.xls]sheet1!a1

i've tried the concatenate and "& &" method to no avail.

View Replies!   View Related
Concatenate Cells And Delete Duplicates
I have an excel 2007 spreadsheet that lists
items on multiple rows for the same customer.

Each sheet will list anywhere from 25 to 100 individual customers.

I need to identify the duplicates, concatenate
the "Items" to a single cell on the first row, and
delete the duplicate rows. eg:

Cust #| Item
1 | A
1 | B
1 | C
2 | B
2 | E
3 | A
3 | C
3 | E

The result should look like:

Cust# | New Item
1 | A, B, C
2 | B, E
3 | A, C, E

View Replies!   View Related
Concatenate Formula (merge Cells)
I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.

View Replies!   View Related
Concatenate A Range Of Cells (A + B + C = ABC)
I know how to do the "normal" type of concatenation (concatenating?), but I was wondering if there was a way to concatenate a range of cells.

If my first three cells look like this:
A1 = Adam
A2 = Bob
A3 = Chris

I'd like cell A4 to say "AdamBobChris". I'm not worried about punctuation at this point. I thought I could just do =concatenante(A1:A3), but that's not working.

View Replies!   View Related
Concatenate 3 Cells Based On Condition
In the workbook, 3 columns of importance Column N,S and AC. Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG. Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.

Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront). Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.

View Replies!   View Related
Merge Cells (Concatenate Through Macro)
I have some records. I want to merge them through macro in a singel cell, like I have selected a range then I want to merge them in active cell with a comma.

View Replies!   View Related
Concatenate Cells Address Style
How can I concatenate 2 cells address style? I'm trying to merge 2 cells into one, so that one is under the other in the same cell.

View Replies!   View Related
Formula To Concatenate Cells With Data
Is there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of

View Replies!   View Related
Concatenate Cells And Add Text
I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.


Assume row 1 and columns A, B, C, D.

496 | 6d7 | Minor | .375 +/-.010

What I need this to read as one long string:

#496, 6d7, Minor, .375 +/-.010

Adding the text in Red.

Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.

View Replies!   View Related
Concatenate & Copy Cells
I have a spreadsheet of inventory items that I am trying to prepare to import into Quickbooks. There are items in this sheet that are subitems of a parent item. The subitems have the same ID number (col. C in the attached sample), different quantities and different descriptions than the parent.

I need to to concatenate the Item Lookup Code (col. D in example) and the description, separated by a colon. Then I need to copy everything else in the parent record, except quantity and description, into the child records. There are close to 20,000 items in this sheet.

View Replies!   View Related
Concatenate Cells For Duplicate Rows
I was trying to create this certain tool using excel which queries data from database. I was successful in obtaining the data I need and paste it to my spreadsheet, however, there are some requirements I cannot create a solution with. Below is a description of my Excel file (which is also attached):

- There are 9 columns on my spreadsheet
- Number of records per data refresh are indefinite, for information in database are constantly being updated
- Several cells on the first column have exactly the same information

The requirements I cannot do are:

- For all cells on first column that have similar values, their corresponding data on column 5 should be concatenated
- Delete rows that have same data for column 1 except the first record found, place concatenated data on column 5 of first record


View Replies!   View Related
Concatenate Each Row To Cells In Column
I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set. I believe the code below is close, but I am receiving this error

Run-time error '91':
Object variable or with block variable not set

Debug points to this line

rngComment = . Cells(intCounter, 5)

I'm not sure what I am doing wrong.

Option Explicit.........................

View Replies!   View Related
Concatenate Cells Into Unique Numbers
I need a mcaro to concatenate my project. I have 1 number in six separate cells that I need to be concatenated into a 3 digit number without duplicates. My data is in cells A4:C5 (six numbers) note that I have two duplicate numbers so the results should only yield (10) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly.

My data in cells A11:C12 (six numbers) are all different so the results should yield (20) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly. I also wanted to know will I be able to edit the macro to concatenate 5, 6, 7, 8 or even 9 numbers into 3 digit numbers without duplicates. If so, how can this been done? The results would be 5#'s:10 results; 6#'s:20 results; 7#'s:35 results; 8#'s:56 results;

9#'s:81 results.

I have included a sample file.

View Replies!   View Related
Concatenate (Join) Cells Across Columns
I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now.

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A2:A300")
For Each cel In rng
x = x & cel.Value
.Range("A1").Value = x
End With

End Sub

View Replies!   View Related
Concatenate Cells, Ignoring Blanks, Then Trim

I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.

View Replies!   View Related
Concatenate Multiple Cells With Array Formula
If I have the following data set in cells A1:C9:

IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878Correct LevelFALSE

In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):


In cell E2, I have this formula to count unique occurrences:


In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):


View Replies!   View Related
Copyright 2005-08, All rights reserved