# Combining Cell Values?

Nov 26, 2008

I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this.

I have tried
=011-A1-11
="011-"A1"-11"
="011-A1-11"

## Combining Cell Values In Column Based On Other Cell Values?

Mar 2, 2012

it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.

## Combining Cell Values?

Feb 8, 2012

I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).

CarPhoneHouseBoatAndrewXAndrewXAndrewXAndrewX

## Combining 2 Cell Values - Without Losing Data

Dec 4, 2013

I have a data set that I'm trying to sort in order to be efficient for some of my team members.

Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.

Col A

123-45678-A-1
123-45678-A-10
123-45678-B-2

I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.

My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.

Col A
Col B
123-45678-
1
123-45678-
10
123-45678-
2

Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:

Columns("B:B").Select
Selection.NumberFormat = "00"

To produce:

Col A
Col B
123-45678-
01
123-45678-
10
123-45678-
02

Now all I need to do is rejoin these 2 columns before sorting. My current code is:

Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value

[Code]...

As many have probably already guessed, this produces the following result:

Col A
123-45678-1
123-45678-10
123-45678-2

I'm looking for:

Col A
123-45678-01
123-45678-10
123-45678-02

**zeroes in BOLD for reference only, result does not need to be bold**

## Combining Text And Cell Values From Multiple Worksheets

Dec 1, 2013

I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.

ActiveSheet.Cells(1, 1).Select
ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' "
End Sub

how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?

## Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.

Jul 15, 2009

I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:

A1 contains B
B1 contains 2
B2 contains "tribbles"

An imaginary function might go like this........

## Combining Values In Bar Graph

Jul 9, 2012

The company I work for has kept a list of the types of objects sold with their houses and how many were bought for each customer, via an Excel document. Here's a snapshot - You can see the name of each type of object on the left, with each column afterward representing an individual customer (Each vertical row is one customer) and how many of each item they bought.

[URL]

We're hoping to make a bar graph of how many of each type of item has been sold. I selected all the titles and columns of numbers and ended up with this, which is almost what we're looking for:

[URL]

The problem here is that each individual customer has their own line on the graph. The Recessed Can Light one I highlighted is a good example of a lot of people who bought varying amounts of that one item. What I'm looking to do is combine the individual sales for each item and make a single bar for each representing the total sales overall, so we can gauge what the best and worst-selling items are. Is there any way to do this?

## Combining Separate RGB Values

Mar 13, 2013

I have a User Form in which users can enter R,G & B values in 3 seperate Text Boxes in order to change the colour scheme of a worksheet to suit their personal taste.

I have tried combining the 3 values into a string to give, for example, RGB(255, 182, 45)

But, of course, because it is a string variable it is enclosed in quotaion marks and so, when add to a .Interior.Color function, the qutation marks are added too and the macro fails.

I have tried altering with Left, Right, Mid and Trim but cannot get rid of the quotation marks. I also tried converting to Hex but got totally confused there!

code to convert the values in the 3 list boxes (called LBRed, LBGrn and LBBlu) into an RGB value that can be appended to .Interior.Color

## Combining Two Area Charts With Different X And Y-values?

May 10, 2014

I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.

In the excel sheet the PV needs to be the x-axis.

## Combining Values In Multiple Lines

Apr 15, 2008

I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.

The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.

Here's a sample of what it would look like:

Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00

So basically the above shows they paid \$25.00 (correct rate) for April 08, then they took back \$20.00 the prior two months (the old rate) and paid the correct rate right afterwards.

In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:

Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00

Just giving the sum of the amounts for a the given people in the same month?

I'm pretty good with VBA but this one is stumping me.

## Combining Values To Create 2 Digit Pair?

Jan 17, 2013

In A1
01234

In B1:k1
01 02 03 04 12 13 14 23 24 34

I am looking for a formula that will combine each digit together as a 2 digit value

Is this even posible?

The value in A1 could range from a 3 to 9 digit value.

## Combining Date Field And Adding Values.

Sep 21, 2006

I have a spreadsheet that retrieves data from a 3rd party app/database. The data that is returned has two dates..

ie..
1/1/051.31.4
1/1/051.31.4
1/2/051.31.5
1/2/051.31.5
1/3/051.32.6
1/3/051.30
etc..

What I would like to see is..

1/1/051.32.8
1/2/051.33.0
1/3/051.32.6

Date range varies, but usually there are two dates retrieved. Date is pulled and display within A2:A700 Range.

## Combining Some Of Rows Values Based On Multiple Criteria (2)

Apr 18, 2014

I have a table based on transport numbers who sometimes have doubles. Example:

Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1

So it works as following:

A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine

So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.

if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.

So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)

## Combining Two Functions Into One Cell?

Jul 23, 2014

I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")

Here is a sample table with showing the data and result

4
5L
4,5L

5
6R
5,6R

5
7L
5,7L

6
10R
6,10R

6
3L
6,3L

## Combining 2 Formulas In 1 Cell?

Jan 13, 2014

I am looking at combining 2 formulas and have them in a single cell. Currently I am using this one =IFERROR(IF(S3-V3=0,"Completed within agreed time!",S3-V3),"") if the value =0 but need another one if there's no value in V3 it would say "Outstanding" I have tried to add IFBLANK to the existing formula but I must be doing something wrong as it doesn't work.

## Combining MAX, ROUNDUP &amp; IF For The Same Cell

Feb 22, 2010

I am trying to create one formula (in one cell) so that it performs the following three things:

1) Rounds any number to the nearest dollar

2) Makes the minimum dollar value \$2.00

3) If another cell (G2) reaches \$100.00 or more, I want it to revert to another cell's (B2) value.

So, in essence, I want to combine the following two things:

=MAX(2,ROUNDUP(F2,0))
AND
=IF(G2>="100",B2)

I do not know how to correctly order these so that it performs all three things I'm looking for it to do. I've posted this before, but was given an answer that created a circular reference and did not make the minimum value \$2.00.

## Combining Cells Into One Cell

Sep 17, 2009

I am trying to combine multiple cells into 1 cell per row.

I would have "A" column empty, then combine "B", "C", and on into the "A" column.

Each cell value will be separated by a space or any special character I designate in the macro

I have attached an example which the output is separated by a space. Sheet1 has the original file and Sheet2 has the desired output.

## Combining SUM With Fixing A Cell Value

Jun 20, 2008

how can I combine the Formula =(SUM(A2:A100))/(SUM(B2:B100)) with setting a value of the cell D2?

What I want would look like this (in column C):

=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=0
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=100
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=200
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=300

and so on.

The point is that the values A2 to A100 are dependent on the input value in D2.

## Combining A Text Cell With A Number Cell

Dec 27, 2009

I am using excel 2007 and have the following information:

Cell A1: L6212- (formatted as text)

Cell B1: 05.50 (formatted as custom number 00,00)

When I merge the 2 cells using the concatenate formula I end up with

Cell C1: L6212-5.5

What I want is L6212-05.50

## Combining Numbers In A Column Into One Cell

Jan 14, 2014

Say I have a column of numbers. I'd like to be able to string them together in the following format: number1|number2|number3....

connect.xlsx

## Combining Formulas In A Cell And Concatenate

Feb 18, 2010

I am trying to combine this formula with another formula but I cant get it right.
I have multiple worksheets that feed into 1 so I am using this formula to pull in the info.

=II.MissionCritical!C20. This info is a title like "Writing".
=II.MissionCritical!C23. This info is a yes or no.

So I need the =II.MissionCritical!C20 formula to also say if c23 is no add an *** to the front of the title. so the title would look like "***Writing". and if C23 is yes leave the title as is.

## Combining 2 Rows That Share 1 Cell

Feb 27, 2013

combining 2 rows that share 1 cell so that it is easier to format cells. For example: A4 and A5 have been combined with B4 and B5 to form 1 cell that has content. How can I convert this to a cell that only occupies A4?

## Combining If And VLookup Formula In One Cell

Nov 19, 2013

I am trying to create one formula that can be copied to other cells to get varied results.

Here are the formulas

1. =IF('Calculated Rental Fees'!H4='Daily Rentals & Charges'!\$B\$28,'Daily Rentals & Charges'!\$B\$30,VLOOKUP('Daily Rentals & Charges'!\$A\$20,LATECHRGETBLE,2,FALSE)*C4

2. =IF(AND(I4>='Daily Rentals & Charges'!\$A\$36),I4='Daily Rentals & Charges'!\$A\$38),VLOOKUP('Daily Rentals & Charges'!\$A\$22,LATECHRGETBLE,2,FALSE)*C4)

Basically i am trying to calculate late fees for a rental company using the if and vlookup functions.

## Index Match Using Cell Combining

Jul 13, 2014

I am trying to use following string to find result into column F and unable to find reason for Error.

=INDEX(\$C\$2:\$C\$5,MATCH(D2&E2,\$A\$2:\$A\$5&\$B\$2:\$B\$5,0))

A
B
C
D
E
F

1
part
code
price
find part
find code
Result

2
x
11
5
y
12
#VALUE!

[Code] ........

## Determining Larger Value Cell & Combining With Another C

Aug 7, 2007

I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?

## Combining A String To Reference A Cell

Oct 10, 2008

I'm trying to switch my vlookups to something more dynamic. I keep adding columns to the data sheet (sheet1 for these purposes) and so I have to go back and update all of the other sheets that use the vlookup. (I'll just give an example as sheet2 for these purposes)

So what I have currently:
=VLOOKUP(A5,Data_All,4,FALSE)
Basically the 'A' column in sheet2 has an integer value, and so the vlookup takes that integer and looks up on sheet1 which row corresponds to that integer and then returns that string value.

What I want:
='AO_Cat_' & 'A5'

Basically I have named every cell in sheet1 and the particular value that I want to pull is named "AO_Cat_%name" with the last part being the integer value. I don't know if that make sense or not. For example, on sheet2, A5 might be '16' and so the value that I want to pull is "AO_Cat_16"

If I were to do this from the VBA side of things, then it'd be a simple range("AO_Cat_" & id_number) where id_number was the lookup value. However, I'd prefer to have this directly as a worksheet formula for simplicity purposes.

## Combining Text From One Cell So That Appears On One Line

Mar 2, 2014

I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.

## Combining Fixed Text In A Single Cell

Aug 20, 2009

I have about 20 choices I'd like a user to select from and have the text output to a single cell.

Just create a Data Validation List? Yeah, but that only allows you to select ONE of the items in the list -- I want to select as few as zero and as many as all 20, combining the resulting text into a single string - space or comma delimited.

My first thought was checkboxes, but all I can seem to figure out is how to get ONE of the results into the cell, meaning I'm better off with the Data Validation List because it's easier.

Especially since I need to repeat this for as many as 200 or 300 lines,

## Combining Text And Cell Reference With Indirect

Oct 2, 2008

I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")

But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.

## Combining Cells Based On Value Of Adjacent Cell.

Jan 11, 2009

I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.

A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,

What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode