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


Excel 2003 :: Concatenate Text Strings From Column Using Multiple Criteria Condition (formula)

Dec 20, 2012

I am looking for a way of creating the following conditioned concatenation.

I have two tables, let's call them "summary" and "detailed".

The "detailed" table is something like the following:





The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:

ID (unique)
VOL (concatenated)

V01, V03, V05

V01, V04


PS: I have people using this table with office 2003, so compatibility is necessary...

View 1 Replies View Related

Concatenate Multiple Strings Of Text With Formatting

Aug 20, 2013

I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.

String 1: When you simply concatenate
String 2: these strings, Excel discards
String 3: formatting at the character level.

Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.

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

Concatenate Two Strings With Date Omitting Missing Information

Jun 26, 2014

I have the following formula:


But if both cells are empty I get : EXP: VP DATE

If only one is empty, i get EXP: 08/26/19 VP DATE or EXP: VP DATE 06/27/14

But I do not want EXP: or VP Date text to show if the content of the AK# or AL# is empty. I figured I will place something like If(ak#="","",

But it give me a "Too many arguments" error.

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

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

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.

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

Attached File : Email Generator.xlsx‎

View 3 Replies View Related

Finding Strings In VLookup Value - Embedded In Larger Strings Within Table Array

Dec 20, 2012

I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array

Essentially I imagine this involves the FIND function at some point.

Attached is an incredibly simplified example of what I'm looking for.

View 9 Replies View Related

Combine 2 Strings Text Into One (IF Formula)

Jan 28, 2013

I have two columns each column has a combination of the same 3 words "cash", "card", and "trade". The are formatted as "Cash & Card & Trade" or "Cash & Card" or "Card" ect. I want a formual that can combine the entries written into each columan into one column. I will put an example below.



both (formula in this column)

name 1
Cash & Card & Trade
Cash & Card
Cash & Card & Trade

name 2
Cash & Card


View 3 Replies View Related

Formula To Join Several Text Strings Together

Jan 17, 2007

I am using the following formula to join several text strings together:


(in the above $A$2 is just a few spaces in order to seperate the test)

I was wondering if there is a way of joing text strings that will align all info properly? Right now it just joiins them together but visually that is not really very appealing.

This is an example of what I am getting:
11 111 1111 11 111
22 2222 22 2222 222
333 33 3333 333 333

This is how I want it:
11 111 1111 11 111
22 2222 22 2222 222
333 33 3333 333 333

View 9 Replies View Related

Formula: Filters On Two Text Strings

Aug 14, 2008

I am trying to write a formula that filters on two txt strings. The table consists of only three columns an ID number, a subject field and a date&time column but the number of rows will change every week as new records are added.

What I would like to do is search for the start of text string then the end txt string compare the ID number to make sure they are the same then work out the number or work hours the call has been open for. This then needs to be repeated for all entries in the table. The table will contain many other txt strings that need to be ignored.

View 9 Replies View Related

What Formula 2 Use 2 Count SEVERAL Strings In A Column?

Aug 21, 2008

I'm a novice trying to figure out the following:

I have a column, where each cell in the column has one of the following "ratings" entered as text:


How do I count the number of times "poor" AND "fair" are listed in the column? I used the following countif forumla to count the # of occurences of the word "poor":


...but how do I adjust the formula so that it counts the # of occurences of the word "fair" AS WELL?

View 9 Replies View Related

Search For Parts Of Strings In Another List Of Strings

Dec 16, 2013

I have an excel worksheet with about 10K rows of data in column A.

I have also another list of data, about 200 rows of data, in column G.

I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.

in column A
row 1:
row 2:
row 3:
row 4:

in column G:
row 1: help
row 2: info
row 3: support

I need rows 1, 3 and 4 in column A to be colored.

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

Formula / Algorithm For Identifying Minor Changes In Text Strings?

Feb 17, 2012

I have a database of approximately 250000 email addresses and I would like to see if there is some way to use Excel to analyse the strings in order to identify potential 'non-unique' individuals.

For example, if I have the following email addresses:

Is there some way of extracting common text strings, lets say where there is a difference of 1 character / digit between strings (rather than alphabetising and searching manually)?

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


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



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


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


Something wrong with the formula? how come it doesn't do both?

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,


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:


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

Formula For Counting Number Of Occurrences Within Range Of Cells AND Strings

Jan 14, 2014

I'm looking for the easiest way to count the number of occurrences within a cell range.

The formula that I'm currently using is:


This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;

When I want to find multiple values in the range and count them all, I use this formula:


View 9 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:


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

Copyrights 2005-15, All rights reserved