Concatenate Function- Two Texts In Two Cells

Aug 1, 2008

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:

JOHN SMITH

The second text needs to be in ITALICS.

ADVERTISEMENT

Using Function To Concatenate Cells

Jan 29, 2009

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 2 Replies View Related

Join Text In Two Different Cells Using Concatenate Function?

Nov 19, 2013

I am trying to join text in two different cells using concatenate function / & operator In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?

View 4 Replies View Related

Color Cells For Some Specific Texts In Excel Sheet

Feb 16, 2014

I want to color the cells for some specific texts in RED, and some in BLUE and some in GREEN.

For Eg: I want to color the cells in RED, which contains below specific texts in a sheet.
UNIX
LINUX
MS OFFICE
POWER POINT....like this for nearly 100 specific texts.

and I want to color the cells in BLUE, which contains below texts in a sheet.
JAVA
.NET
SQL
MSDOS....like this for nearly 100 more specific texts.

If i create a rule in conditional formatting for each text,I have to create nearly 400+ rules for my task now, which is not possible.

View 5 Replies View Related

Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsm‎

View 10 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two
one

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

Concatenate Function..

Jan 23, 2010

What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com

View 2 Replies View Related

Array Concatenate Function VBA

Aug 8, 2012

I use a existing code and want to add the Concatenate in the code.

VB:
Sub Subtotal()

Dim myAreas As Areas, myArea As Range, x As String
Dim y As Long

Worksheets("INVOICE_hulp").Activate
Range("Q1").Select

[Code] ....

The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?

View 9 Replies View Related

CONCATENATE Function (dropping The 0 From B2)

Feb 16, 2010

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
C12A

View 2 Replies View Related

Vlookup But Do Not Want To Use Concatenate Function

Mar 28, 2007

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 14 Replies View Related

Concatenate TODAY() Function

Feb 23, 2010

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 2 Replies View Related

Trying To Insert Nested Concatenate Function In VBA

Nov 7, 2012

I am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T

he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is

=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).

VB:
Dim iInput_Rows As Integer
iInput_Rows = Worksheets("Workspace").Range("D3").Value
Dim iOutput_Rows As Integer
iOutput_Rows = Worksheets("Workspace").Range("D1").Value
Dim iAnalog_Rows As Integer

[Code] ....

My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?

VB:
Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"

View 2 Replies View Related

Concatenate Formula With If And Text Function?

May 20, 2014

I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:

=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")

However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.

It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))

I think it is trying to continue doing something with the text function

View 3 Replies View Related

Function Inside Concatenate Funcion

Dec 18, 2007

how i get this to work?

B4 = BMEF3

function:
=CONCATENAR("=TZMS|";B4;"!VAR")

and this its what should do:
=TZMS|BMEF3!VAR

how im supposed to do that function work?

View 14 Replies View Related

Carriage Return In A CONCATENATE Function

Jan 14, 2009

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 4 Replies View Related

Concatenate Function To Merge Certain Columns

Aug 14, 2009

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 5 Replies View Related

Creating Unique ID Using Concatenate Function

Mar 26, 2014

I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.

View 3 Replies View Related

Function To Concatenate Cell Values

Jan 10, 2012

I need to concatenate all cells filled out in a column (for example A in sheet1) in a cell in sheet2. More exactly.

column A (Sheet1) (Sheet2)
aaaa cell(2,2)=aaaabbbbcccc
bbbb
cccc

If I use a VBA macro I can write this VBA code

RowsNumber = Application.CountA(Sheet1.Range("A:A"))

Then loop from 1 to RowsNumber and concatenate values in cell(2,2) but I need, if this is possible, to define a FUNCTION (nested functions) in cell(2,2).

View 2 Replies View Related

Excel VBA - Use Concatenate Function To Show A Value

Jul 12, 2013

Possible to use concatenate function to enter a formula to a cell and have the cell show the result?

I enter the following in a cell:

=CONCATENATE("=LEN(""",A2,"""",")") 'A2 contains the string "Exchange rate: 925"

what is shown in the cell is:

=LEN("Exchange rate: 925")

If I manually enter the above len function in the next cell, the cell displays 18.

Is it possible to use the concatenate function, maybe combine with other functions, to display 18 in a cell?

View 5 Replies View Related

Underline Word In Concatenate Function?

Apr 22, 2014

each cell contains one word say hello in a1 and Henry in b1. Is it possible to underline Henry after =concatenate(a1,b1) ?

View 4 Replies View Related

Concatenate Function ... Same Spread Sheet Different Tabs

Mar 19, 2009

I want to concatenate two columns in a separate tab but when I do, the values appear as ....

View 9 Replies View Related

Use Input Box Result As Part Of A CONCATENATE Function

Jun 21, 2006

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 3 Replies View Related

Concatenate Function To Cell Macro Code

Mar 8, 2008

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 2 Replies View Related

Concatenate Function It Appears To Put Quotes Around The Entire Result

Feb 13, 2008

=IF($A2="","",IF(O$1="","",VLOOKUP($A2,HQA01!$A$24:$AG$525,6,FALSE)))

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.

=IF($A2="","",IF(O$1="","",VLOOKUP($A2,$Q$1&"!$A$24:$AG$525",6,FALSE)))

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

View 9 Replies View Related

CONCATENATE Function To Merge 2 Strings Together Into A Single Cell

Apr 20, 2009

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 5 Replies View Related

Calling Value From Sheet And Send It Back In Concatenate Function

Jul 23, 2014

how to properly write this kind of commands!

v_1 = Sheets("WB_Input").Cells(i, 2) 'It's gonna be a numeric value like 10
v_2 = "E"
v_3 = Sheets("WB_Input").Cells(i, j) 'It's gonna be a numeric value like 12
Sheets("WB_Output").Cells(i, j) = "=CONCATENATE(" & v_1 & "," & v_2 & "," & v_3 & ")"

Thing is once I look at the WB_Output Sheet what I see is an error and the cell shows this: =Concatenate(10,E,12)

So logically the problem is that I need to make it show up as =Concatenate("10","E","12")

Btw, I can't just put v_1 & v_2 & v_3, cause Excel understands "E" as exponential!

View 1 Replies View Related

Concatenate Function To Join Some Text Strings To A Cell On A Different Sheet

Jan 11, 2010

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 3 Replies View Related

Sum Value From Texts

Oct 14, 2009

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 9 Replies View Related

Sum Of Numbers Between Texts

Apr 29, 2009

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 9 Replies View Related

Concatenate (two Different Cells Into One)

Dec 11, 2008

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 4 Replies View Related

Concatenate Several Cells On Tab One

Apr 11, 2013

On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date. Any way to lock the date format for this idea?

View 7 Replies View Related

Concatenate Cells With % In Them

Apr 27, 2007

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 9 Replies View Related

Substituting Texts From Specified List

Dec 1, 2009

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

2232-2232-

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:

20-20-

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

2210-22G1-45R1-45G1-20R1-20GP-
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 9 Replies View Related

Format: Concatenate Value Of The Several Cells At One

Apr 30, 2009

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
40/250/300

View 5 Replies View Related

IF OR And CONCATENATE To Read The Cells

Jun 1, 2009

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 2 Replies View Related

Concatenate A Range Of Cells (A + B + C = ABC)

Sep 29, 2009

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 4 Replies View Related

Concatenate The Cells On Condition?

Apr 23, 2014

I have a Requirement where we need to concatenate the cells based on conditions

i have the attachment for the reference . please download the file from the link.

View 1 Replies View Related

Concatenate Cells And Add Text

Feb 6, 2009

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.

Example:

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 5 Replies View Related

Using For Loop To Concatenate Cells

Apr 14, 2014

I'm trying to concatenate a few cells but seem to be missing the mark with my code. show me where I went wrong.

Code:

Dim counter As Long
Dim b As Range
Dim e As Range
Dim g As Range
Range("E1").Select 'Set the range for the loop
Selection.End(xlDown).Select

[code]....

View 5 Replies View Related

Can I CONCATENATE Blank Cells

Jul 17, 2007

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 9 Replies View Related

Concatenate Multiple Cells

Nov 25, 2009

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.
Range("N1").Select
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
Loop
End Sub

View 9 Replies View Related

ADVERTISEMENT