Concatenate Formula To Combine Two Cells Of Data

Sep 8, 2012

I am attempting to use concatenate to combine two cells of data, but to only display the information if both cells contain information.

Currently using the following simple formula:
=concatenate(a1,a2)

However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.

View 3 Replies


ADVERTISEMENT

Concatenate- Combine All The P.O# With A Formula Automatically

Mar 13, 2009

Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.

Log # Amount P.O. Numbers 1-a 45,678 1234 1-b 89,173 5678 1-c 66,526 9123 2 22,113 2345 3-a 66,654 6789 3-b 88,650 2468

1 201,377 1234/5678/9123 2 22,113 2345 3 155,304 6789/2468

View 10 Replies View Related

Formula To Concatenate Cells With Data

Feb 6, 2009

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

Combine Pivot Lookup And Concatenate?

Dec 15, 2013

I have a question to joint 3 functions : pivot>lookup>concatenate, to make it easier please have a look below table'

work hour
Dept
Machine

24
Seed
Seed-01

24
Seed
Seed-02

24
Seed
Seed-03

[code]....

From above basic table I need to make it compact to below format:

Dept
Work Hour
Machine

Seed
18
Seed (04,05)

24
Seed (01,02,03)

[code]....

what formula would suit the format above.

View 1 Replies View Related

Concatenate Formula (merge Cells)

Oct 14, 2009

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

Concatenate Cells To Create Formula

Jan 17, 2007

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

Concatenate Multiple Cells With Array Formula

Jan 21, 2009

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
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE

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

=COUNTIF(A$2:A2,A2)=1

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

=SUMPRODUCT(C2:C9*1)

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

=IF(ROWS(G$2:G2)

View 9 Replies View Related

Formula To Combine Cells To Build Email Address

Oct 16, 2008

I have a worksheet with three columns. First Name, Last Name and email address. I need to combine the first name with the last name and add our @ourcompany.com to the end of it for the emaill address.

I've figured out how to do that, but my problem is, we don't use our first and last names in the email address. We use first initial and last name.

I know how to make Tom Jones become tomjones@ourcompany.com

What I need to know is how to make Tom Jones become tjones@ourcompany.com.

Can someone point me in the right direction here? I just need the first initial of the first name and combine that with the last name and then add in the @ourcompany.com.

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

Combine Data From Multiple Cells ..

Dec 11, 2008

Is it possible to combine datas from over 200 cells (all cells are from the same row) in one cell? I have tried CONCATENATE function and e.g. =A1&B2&... but is there shorter formula for this?

View 4 Replies View Related

Combine Data From 2 Cells Into 1 Cell

Mar 13, 2013

Let's say there is a name (Big Bird) in cell A1

Let's also say that cell B1 has =Today()

How can I make cell C1 = Big Bird 3/13/2013?

View 9 Replies View Related

Combine Data From Diff Cells Into One Cell

Aug 27, 2009

I have 5 columns, each with some data. On the 6th column I need to combine the data from all 5 columns into column 6 to be as: Data in A2-Data in B2-Data in C2-Data in D2-Data in E2. With one exception. column D is the date in this format 27-Aug-09. I need the date to be modified on the fly from 27-Aug-09 to be 270809 which will be the info in D2. See example attached.

View 3 Replies View Related

Merge And Combine Data In 2 Cells To One Cell?

Jun 21, 2013

I need a macro, that will merge the cells and the data in cell A1 with data in B1, and the data in cell B1 will paste to the end of data in cell A1.

Column A Column B
KL8CB6S90DC505233
3GYT4MEF9DG331323

I need the final result to look like this:

Column A
KL8CB6S90DC505233
3GYT4MEF9DG331323

I need it to complete this for all data in Column A and Column B.

View 3 Replies View Related

How To Combine Cell Data From Many Cells In A Column

Mar 3, 2014

21045

21022

21054

20214

20114

I would like to know how to combine many cells in a column - sometimes 100's - into one cell separated by a comma without having to type each cell name using.

View 4 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:

TABLE 1:

Project
Benefit Type
Delivered or Enabled
Benefit

PJ1
Financial
Delivered
Saving of $4M over 24 months.

[code]....

I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.

TABLE 2:

Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled

[code]....

So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

Concatenate Formula Nested Within IF Formula Is Not Producing Expected Results

Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:
D5: Middle Name:
D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsx‎

View 3 Replies View Related

Combine Formula Which Reduce The No. Of Additoanl Formula's Columns

Apr 9, 2009

I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.

To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.

Is There any formula to show fail subject in Remarks column without creating 5 additional columns?

View 14 Replies View Related

Macro To Combine Cells In A Row To 1 Cell While Skipping Blank Cells

Jul 2, 2014

I need to combined all non blank cells in a given row into 1 cell. But within each row i have 5 phases that the values fall into, which is denoted by the 1st charter 1, 2, 3, 4, or 5. for example, in a given row i have 1-a, 1-c, 2-d, and 1-f and these values occurs in non consecutive columns starting from G to ALR. I need a macro that sorts these values in one of the 5 phases. So in another sheet the macro would combined [1-a 1-c 1-f] in Phase 1 and [2-d] in phase 2. and if there are other phases it would put them in the appropriate cell.

See attached workbook : Work Order Summary Sheet.xlsx‎

View 3 Replies View Related

Macro To Combine Cells In Row To 1 Cell While Skipping Blank Cells

Jul 17, 2014

However, in addition to what the macro already does, it is possible to add another work sheet in the same work book that outputs the values in separate cells? It also has to output the data in order: for example, in the first work sheet the data is inputted at random and has spaces but the macro will have to remove all the blank cells and output them in order based on the first value in the test string, 1, 2, 3, 4, and then 5;

input:

blank 3-x Blank 1-y blank 2-z 1-k

output:

1-k 1-x 2-z 3-y

View 4 Replies View Related

VBA Concatenate Formula

Jul 25, 2009

I'm trying to use a macro to write a formula within a column of data.

Here's the

View 3 Replies View Related

Combine Text Entree In Formula With Formula?

May 21, 2012

Is there away to Combine a Text Entree in a Formula with a Formula

=IF('VEGAS - Main'!Z9=1,"MIXED DBLs","")

I am trying to do something like this:

=IF('VEGAS - Main'!Z9=1,"TEAM EVENT"+'Mixed Doubles'!P9

Result will be MIXED DBLs 1st Place

View 5 Replies View Related

Concatenate The Number Into A Formula

Oct 14, 2008

I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?

E2 is 134

=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")

returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...

View 3 Replies View Related

Short Formula Instead Of Using Of CONCATENATE?

Apr 19, 2014

I am using CONCATENATE formula to make summary of my data, but i m using long CONCATENATE formula. I want to use short formula instead of CONCATENATE

formula is here :

[Code]....

How to short this formula and accuracy is first ...

View 7 Replies View Related

How To Use CONCATENATE And SUBSTITUTE Formula Together

Nov 17, 2011

Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet

PHONE

WIRE
CANDY
INDIA

When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this

PHONE WIRE CANDY INDIA

I want it to look like this, removing any blank lines within cell A1

PHONE
WIRE
CANDY
INDIA

Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")

View 1 Replies View Related

Concatenate Formula Amendment

Oct 14, 2009

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.
Is this possible? I've tried to use the formula below with no luck,


=Concatenate(A1:GR1)

View 9 Replies View Related

Make Concatenate A Formula

Feb 24, 2010

I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:

=NeoTicker|q!'@ES#,last'

I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:

G10: '=NeoTicker|q!'
H10: '@ES#

View 9 Replies View Related

Concatenate Formula For Numbes (% And $)

Aug 7, 2006

I have been able to create concatenate formulas for numbes (% and $), but have been unable to create one that will display a date, I. E. for the number formula, I used, =(A8&" "&TEXT(B8,"$0")). What would be the formual so the second cell show the value as a date?

View 2 Replies View Related

Concatenate Strings In A Formula

Aug 10, 2007

I am writing a macroI have three variables named "name", " book", and "count". In previous statements these variables have all been defined as String, String, Integer respectively. I am trying to uses all of these variables in the same formula and am encountering issues. The formula I wish to use it in is:

ActiveCell.FormulaR1C1 = "=COUNT('[" & book & "]" & name & "!'""B6:B" & count & ")"

Where if book = Book1 ; name = "Sheet1" ; and Count = 100 I would want the following result: "=COUNT('[Book1]Sheet1!B6:B100)"

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







Copyrights 2005-15 www.BigResource.com, All rights reserved