# If Cell Has X Turn Into Number And Concatenate

Jul 4, 2009

Example is in the attch: Book11.xls. This formula works but if there is no number 4 then I don't want comma after 1, or 2, or 3, How avoid it:

results

1,2,3,4
1,2,
1,2,3,
3,4
1,
4
2,3,

(red , need to be removed)

## Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red

Dec 10, 2008

can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

## Turn #DIV//0! To 0 Or Even Turn It To A Blank Cell

Nov 6, 2009

I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).

The problem is that if there is no data in a participants column then excel correctly gives you readout of #DIV//0!. On my results page this #DIV//0! makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0! to 0 or even turn it to a blank cell?

## Concatenate Cell With Customized Number Of Zeros

Sep 20, 2013

I have a sheet where some data is entered in A1 to C1 and i use concatenate to string them together. The problem is in cell C1, where I used cell format to customize the number of zeros in front of the number.

EG:
A1: Photos
B1: 2013
C1: 00001

the concatenate result is Photos2013\1, instead of the desired result of Photos2013\0001.

## Concatenate Or Combinate Number A Cell Into Other Cells

Sep 19, 2006

How do I get my number in one cell to show all possible combinations of it in other cells. (e.g. cell B4 = 123, now i need cells C4:H4 to display all 6 combinations of the number as follows: 123, 132, 213, 231, 312, 321). Attached is a sample file.

## Turn Number Into Text?

Feb 21, 2014

Is there a formula I can use that will look at the following in an excel cell Daily Numbers Report - Summary_2014-01-26.xls

and then convert that into the date value 41,300. And the same thing for all other cells with the same format...so if was Daily Numbers Report - Summary_2014-01-23.xls the formula would convert it to 41,297.

## VB Macro That Will Turn Number To Date?

Jul 5, 2012

is it possible to have a VB macro that will turn a number to a date?

For example the numbers are like 21021967 that need to be turned into 21/02/1967

## Minus Sign - Turn Into Negative Number Without Manually Keying Each One?

Feb 14, 2013

I have a column of numbers which have the - sign at the back end of the number instead of the front. What would be the easiest way to turn that into a negative number without manually keying each one?

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

## Concatenate Values With Same Number

Feb 6, 2010

I need VBA that will make those two columns:

1 A
1 B
2 C
2 D
2 E
2 F
2 G
3 H
3 I
3 J

into those two columns:

1 A,B
2 C,D,E,F,G
3 H,I,J

## Concatenate Variable Number Of Cells?

Sep 15, 2014

I have two columns - A and B. In column A there are blank cells and filled cells. There is a blank cell and after it - a variable number of filled cells, then a blank cell again and a variable number of filled cells. I am trying to concatenate the filled cells from column A into a single cell in column B, which is corresponding to the blank cell in column A. Values should be comma delimited.

For example: A2-blank; A3-27; A4-63; A5-blank; A6-31; A7-blank .. and in column B should be B2-27,63; B3-blank;B4-blank; B5-31,B6-blanc and etc.

## Concatenate Based On Column Number

Jun 6, 2007

I need to cocatenate anything between and including column 4 and col (unkown until macro is run)

is there a way to do this?

Basically need to add all the cells from 4th column to whatever col is. Example if col = 12

then I need columns 4 to 11 all rows together

## Macro Or Equation To Have Cell Turn Red / Green Based On Value Of Another Cell?

Oct 23, 2013

I am attempting to create a ROI calculator for my place of work. I want a blank cell to fill in green or red based a cell below it being equal or greater than 5.0%. What is the Macro or equation to do that?

## Concatenate In A Vlookup To Search For A Resource Number

Mar 4, 2010

what i am trying to do is use concatenate in a vlookup to search for a resource number and date, then return another column in the array.

the formula looks like:
=VLOOKUP(CONCATENATE(D7,\$H\$6),Roster_Allocation,7,FALSE)
but only results in NA.

if i search for the resource number only, i get the correct result.
also, the res# and date are concatenated in the table array. could this be related to the way excel is storing the dates (40241?) even though both concatenated fields look the same?
i have also tried adding a new coumn which has the res# and dates concatenated as the lookup value but still all NA.

## Can I Turn A Cell Into A Drop Down

Sep 7, 2009

I know I can right click in cell and choose from the items, but can I actually make the cell a drop-down box, like on websites? Meaning, can I choose which options/numbers/letters/choices I want in the drop down, and be able to right click and highlight one?

## Concatenate Text And Numbers And Then Sort On Original Number

Jun 26, 2013

I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?

Here's an example

A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5

I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:

A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5

Each column is sorted by the numeric value, but has the name label from column 1 attached to it.

## Turn Row Color Based On Cell Value?

Dec 23, 2013

how do I turn a row range a different color based on an answer in a cell value for example:

If B4 = Wont have
Turn grey the range C4:G4

## Turn Part Of Cell Into CAPITALS

Sep 30, 2007

I run a catalogue of music (thousands of records) made in excel
where I have in the first cell the name of the band + album + format,
something like this:

Pearl Jam - name of the album - CD

how can I turn automatically all names of bands into CAPITALS, in
example:

PEAR JAM - name of the album - CD

Is there a way to turn in CAPITALS all text from the beginning of a cell 'till the symbol with spaces on both sides ( - ) of each cell ?

I know how to turn the entire cell into CAPITALS using the formula =upper(column nş) but not just part of the cell.

## Can You Turn Cell Contents Into A Comment

Dec 19, 2008

I was wonder if there was a way to automatically turn the contents in cell B2 into a comment for cell A2?

Is there a formula or some kind of automated macro or function?

## Make Text In Cell Turn Red After The 90th Day?

Apr 21, 2013

I am calculating service dates for trucks: Need to make the text in a cell turn red after the 90th day? A

## How To Turn Range.find Into A Cell Reference

Dec 9, 2009

I am writing a function that opens up an external excel document, identifies a string in a certain range and then i also want it to return a cell reference for that string including the number of rows beneath the cell that the string was found in.

## Turn Blank Cell To Hyphen In Particular Cell?

May 12, 2014

I want to add a hyphen to 2 particular cells if they are blank. The form is populated from another software package with dimensions. If Columns D and E have any blank cells, want to insert a hyphen. Can't use VB because the particular software package will only output to a template with .xlt

If can't do with Conditional Format, then okay I can tell the inspectors to use add by hand

## Use Conditional Formatting To Turn Cell Red If Any Cells Above Are Blank

Nov 3, 2009

I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.

## Cell Formatting - Turn Entire Row To Light Gray

Dec 13, 2013

IF the list value in D2 is = to MT,GP,or H then I need the entire row 2 to turn light gray.

## If Cell Equals New PO Turn Entire Rows Text Red And Bold

Mar 2, 2014

Code that will turn the entire rows text bold and red if a cell in that row has the word.

New PO

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

## Conditonal Formatting (cell Automattically Turn Red Once The Date =NOW() Is The 15th Sept)

Sep 15, 2009

I have worked out the conditional formatting but i have a question. The cell has a date which is input manually - no formulas or anything
it reads 14/09/09. however can i have the cell automattically turn red once the date =NOW() is the 15th sept??

## 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), ", , ", " ")
one, two, three, four
one, two, three,
one, two
one ,

## If / Concatenate Into One Cell

Nov 14, 2008

I have 6 columns:

A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2

I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).

So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")

Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".

## Concatenate IF Cell Is Not Empty

Dec 4, 2013

I have been sent a very large file of product data from one of my suppliers, They sent all of the details in separate fields (for example, colour, height, width, depth, material etc)

I have processed all these columns to appear how I want them, but now need to join them together so they can all be added to my main description. I want each one to be on a new line when it's displayed on my website, so I'll be adding a <br /> tag (break tag) after each item. This I can do no problem.

This would all be fine, if all the columns contained data, but a lot of the time the columns do not contain data (eg. I have a column for "knife length" but not all of my products are knives, so not all of them require this information"

for example, I have something like:

SKU | Colour | height | width | depth | material
ABC | blue | 50mm | 10mm | 60mm |
BCD | | 80mm | 75mm | 30mm | wood
CDE | red | | | | plastic

I could use something like :

=CONCATENATE(\$A2, " <br /> ", CHAR(10),\$B2, " <br /> ", CHAR(10),\$C2, " <br /> ", CHAR(10),)

This however means writing a piece of code for each cell I want to join, My cells go from range "O2" to "DW2", I don't fancy writing that for every single column!! Also, it means that If I have some empty fields, it will still add a break tag, this will look very odd on my site.

I really need a way of writing into the function "include if cell contains data" for each cell. If it doesn't contain anything, skip it and move to the next. (the char(10) in this just gives me a line break in excel so I can see what it'll look like when it's exported)