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


ADVERTISEMENT

Concatenate 2 Columns To Make Every Possible Combination

Jul 19, 2009

Is there an easy way to concatenate two columns and provide every possible combination?

E.g.,
A B
1 A1 B1
2 A2 B2
3 A3
The function should result in
C
1 A1B1
2 A1B2
3 A2B1
4 A2B2
5 A3B1
6 A3B2

View 3 Replies View Related

Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell

May 2, 2014

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).

number_forma.png

I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";

custom_format.png

As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:

custom_formatted_cells.png

As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:

CONCATENATE("SOME STRING ", B1)

Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:

concatenate_formula.png

As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.

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

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

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

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

Conditional Formula To Concatenate Names

Sep 21, 2009

I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.

View 3 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 To Create A Linking Formula

Jan 24, 2006

I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)

In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.

View 10 Replies View Related

CONCATENATE Formula To Put All The Text On Different Lines?

Jul 21, 2006

Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.

View 13 Replies View Related

Format TODAY() Within A CONCATENATE Formula

Nov 28, 2008

Looking for a little help with the following formula =CONCATENATE("August 07 -",TODAY()," Evening Instructor") After entering this formula today is displayed as a serial number. I'd like it to display as current month/current day/current year (11/28/08)

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

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

Refering From Another Workbook By Using The Concatenate Formula

Aug 2, 2006

I would like to reference information from another workbook. Instead of writing the Name of the other workbook I would like to reference through a Concatenate formula (e.g. CONCATENATE("Test"&G2&".xls")). I always get an error message. Is there any way I can reference from another workbook by using the Concatenate or any other formula?

View 5 Replies View Related

Concatenate Worksheet Names For Formula

Oct 19, 2007

I want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.

View 4 Replies View Related

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

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
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C

View 5 Replies View Related

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

Concatenate Formula Referring To Cell In Another Workbook

Feb 9, 2007

"Smith B" is text in C1.

My current formula in C3 is a simple reference to a cell in another workbook:

=+'Z:Time Records2007[Smith B.xls]Daily Time'!$C1352

I would the formula to refer to C1 to get the "Smith B" part.

This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.

To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?

=+'Z:Time Records2007["C1".xls]Daily Time'!$C1352

View 5 Replies View Related

Concatenate Formula To Retain Cell Formats

Jun 11, 2008

I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....

View 6 Replies View Related

Excel 2003 :: Concatenate Formula With Numbers With Decimals

Jul 12, 2012

I'm using excel 2003 and have a problem regarding some code.

Dit(a, b) = "=" & Hit(a, (d - 12 - e) + f) & "/" & (Pro & ".NrE.sol")

Where "Hit(a, (d - 12 - e) + f)" can be numbers with decimals.

When I run the code the result is nothing, unless the number is a number without decimals.

If I use just "Dit(a, b) = Hit(a, (d - 12 - e) + f)" it shows the right number.

View 2 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 Formula Not Taking Decimal Places/currency Sign

May 26, 2009

i have lets say a table with rows that contain the following data.

SKU - PRICE - SALEPRICE - DESCRIPTION - SALEDESCRIPTION

What I am trying to do, is to make a concat formula that says:

SALEDESCRIPTION = DESCRIPTION+price was+PRICE+now+SALEPRICE

In essence so that i can simply pull down the formula, and all our product descriptions have 'special offer, was xx (price) is now xx (sale price).

This works fine, except that in the SALEDESCRIPTION, the amounts from the price and saleprice fields are being brought over without decimal places and without currency symbol.

I have tried formatting those two columns as currency or accounting but neither works.

View 4 Replies View Related







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