Join Digits From Multiple Cells While Retaining Custom Formats

Apr 2, 2009

I have four different cell.

Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.

Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.

View 7 Replies


Custom Formats For Two Different Cells

Feb 20, 2007

i've inherited a spreadsheet that is causing me a problem with a custom format. I have two cells each with different custom formats. The first (cell A) is formatted as thus 0.0;0;. The second ( cell B) formatted as 0.000;0;

The number in each cell will vary. Cell A is always positive and cell B could be positive or negative. The problem occurs in Cell B when this should be a negative number as it picks this value up from another cell ,say cell C. so Cell C= -0.614 but cell B shows this as being 1. I'm sure the author of the sheet did this for a reason. Why does the cell display the number 1

View 3 Replies View Related

Retaining Both Cell Formats When Pasting On On Other

Nov 28, 2008

I have a template A that has lines on it, sometimes I want to overlay template B which has other lines on it, however when I past template B on top of A the formats of A are overridden by the formats of template B.

Is there any way of "overlaying" B on top of template A without losing the template A formats?

View 7 Replies View Related

Highlight Column And Row Of Active Cell But Retaining Previous Formats

Feb 28, 2014

I have found this code by searching witch is perfect

If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target 'With the cell that was selected,
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True

The point is that i want to retain the previous formatting of cells when i select another cell. And also i would like to create a switch to turn the Worksheet_SelectionChange event on and off. The tricky part is here i guess, becouse i would like when turned off to retain the previous formatting also.

View 2 Replies View Related

Join Text Based On Value Of Left Digits

Jul 5, 2007

I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!! The formula I am trying to do is something like this...

In column S:S
IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1"
IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2"
IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3"
IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"

Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.

View 7 Replies View Related

Join Text From Multiple Cells To One Cell

Apr 27, 2009

i know this is easy but i don't have an idea how to do it. I have dates written on column A1:A7 like


what I would like to do is how can I combine 29-Mar-09 and 4-Apr-09 into one cell...I would like the output to be placed in cell A8 appear as "29-Mar-09 to 4-Apr-09".

View 4 Replies View Related

Join Multiple Cells Into Single Cell

Jun 19, 2008

i need to copy the values from more than one cell and need to paste all the values in the single cell (if possible values seperated by commas).

View 2 Replies View Related

Custom Number Formats

Mar 20, 2009

I need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.

View 4 Replies View Related

Custom Time Formats

Sep 8, 2008

I've been trying to create a work schedule and I am having trouble formatting it just the way that I want it. Basically, I am looking to put in the time with an AM or PM after it.

The problem that I am having is that if the time is on the hour I want it to display as 4 PM and if it is not on the hour, then I want it to display as 4:30 PM. I don't want 4:00 PM with the 2 extra zeros. Is there any way to do this with conditional formatting or some type of formula? I just don't want to have to change the number format every time I have to enter a time that is not on the hour.

View 9 Replies View Related

Custom Cell Formats

Jan 27, 2007

I would like to display the following number of hours as day hour minute format
48.0 hrs as 2d 00h 00m.

View 4 Replies View Related

How Do I Delete Custom Number Formats

May 17, 2006

I need to create some custom number formats, but after a few failed tests I
have a lot of junk that I'd like to remove. I've checked the Microsoft
knowledge base which suggest there should be a delete button, but I don't
have one.

View 14 Replies View Related

Linking To Custom Number Formats?

Nov 11, 2011

I want to display a cell with the value -1,000 as Negative 1,000. In custom number formats I have selected #;"negative" #,###;; which does indeed display the cell with -1000 as Negative 1,000, however, I want to be able to link to this cell and have the link pull in Negative $1,000.

View 9 Replies View Related

Delete List Of Custom Formats

Feb 28, 2013

When you right click on your mouse, and you select:

Format Cells...NumberCustom
You can see many formatting styles.
Some of them impossible to delete eg: "General" or "@"

But some others easy to delete one by one pressing the Delete button.

In my Workbook eg "#.##0,00 ;[Red]-#.##0,00 ;; @"

Is there any way of deletting all of then by vba.

I mean all the different custom formats that I am carrying from book to book, from job to job long time ago.

I would like to have just the ones excel gives us by default, not the rest.

View 1 Replies View Related

Custom Formatting On Multiple Cells

Dec 24, 2012

I am creating a spreadsheet for a stockroom, each product that my business sells, is on an individual row, and the details for each product can be seen in the columns (e.g. Product ID, Product Name, Cost Price, Number Sold, Number Brought, Units in Stock).

What I want to happen, is for rows to be blacked out (through formatting) if the remaining stock levels fall below around 3, then the row that the product is on, is 'un-blacked out' and so can be seen. However if the number of remaining stock is above 3, the row is blacked out.

So the sheet will work that only products with the number of remaining units in stock are below 3, are visible, and the others are blacked out.

View 3 Replies View Related

Merging Cells And While Retaining The Same Format

Jan 31, 2007

I have cells that contain number that have dashes

For example

Cell M contains the number 001-1234
Cell N contains the number 1234567 (I have formatted the cell to appear with a dash at the beginning -1234567
Cell O contains the number 891 (I have formatted the cell to appear with a dash at the beginning -891)

I want to combine the three cells and have the number look like this

Is there a formula that will do this

I have tried the Concatenate to combine the cells but it just combines the numbers and won't let me format the number as I want it.

View 9 Replies View Related

Join Text To Multiple Lines

Oct 27, 2006

For the following formula:


The result is:


Is there any kind of modifier that I can use to force the output to multiple lines, i.e.:


If this wasn’t a formula I could just use ALT+ENTER. Wrapping the text will not work because I need fixed break points not dependent on the text box width.

View 5 Replies View Related

Join 2 Cells In VBA

Dec 6, 2007

I'm writing an Excel Macro and want to sum a number cell with the value "45667" and a text cell with the value "Prague" and return it to a new cell with the value " 45667 Prague"

I've done it successfully with the two text cells "John" and "Hopkins" to "John Hopkins".

This is my

Sub SumCells()

Dim FirstName, SecondName, Zipcode, City, fullname, fulladdres, space

FirstName = range("cell1").value
SecondName = range("cell2").value
Zipcode = range("cell3").value
City = range("cell4").value
space = " "

fullname = firstname + space + secondname
fulladdress = zipcode + space + city

The problem is that city is recognized as "Prague", and the zipcode as 45667 (without the "")

View 3 Replies View Related

Join Cells & Compare To Single Cells

Jul 5, 2007

i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. I’ve Highlighted in ROW 947 one that should Say NO yet Says YES.

View 3 Replies View Related

How To Join Cells In Different Columns

May 31, 2012

I have a list containing hundreds of rows and I need to join the cells in different columns.

Eg. Col A, Row 1 is "Red Maple"
Col B, Row 1 is "Acer Rubrum"
Col C, Row 1 is "60mm"

I need to join the row into one cell reading "Red Maple Acer Rubrum 60mm"

View 2 Replies View Related

Join Two Cells Of Data

Apr 20, 2009

I want to take a cell with a persons first name and a cell with their last name and combine into one.

What I have.
A1 B1
John Smith
Walt Smith

What I want.

John Smith
Walt Smith

View 9 Replies View Related

Join Text To Cells

Jan 10, 2007

I have a column with numbers in each cell. I need to add the same three letters before each of the numbers. Example:

number in cell: 25067 I need it: ABC25067

I did a macro for it but had to do each one individually. I imagine there is a simple formula to do this automatically but I don't know enough to figure it out (despite reading up on it for the last hour!)

View 9 Replies View Related

Join Cells With Space

Aug 7, 2007

I have a sentence in field A1 and a number in field A2. The formula I am using is =A1 & A2. This is working but there is no space in between A1 and A2. How can I add a space?

View 2 Replies View Related

Join Text From Adjacent Cells

Oct 21, 2009

I am trying to append data in let's say column A:A with the variable text in J:J. The data in column A:A will look like this /dir/dir/dir/<from this point is where I need the data appended>.

View 4 Replies View Related

Combine/Join 2 Cells Into 1 Cell

Aug 23, 2006

I have a document that has 3 colums.

Complete Name | Surname | First Name

The Complete Name is currently blank. The others just list the users Surname and First name. In the Complete name I need it to have Surname,Firstname - for example

Complete Name | Surname | First Name
Jordan,Michael Jordan Michael

Is there a Formula (Not Macro) that I can use to combine the Surname and First name cells to populate the Complete name cell with the surname,first name???

View 8 Replies View Related

Vba To Join Values In 2 Cells Into Another Cell

Sep 28, 2006

I am trying to write some VBA to join the values in 2 cells into another cell for a list, however my knowledge at this level is a little lacking. For each row in a list I would like to join the value of the cell of Column A and the value in the cell of Column B and write the output to Column C for that row, as shown in the attached example. Column C in the example shows the expected output.

View 4 Replies View Related

Join 2 Cells Omitting Last X Characters

Jul 28, 2007

I'm trying to merge two cells, But i want the second cell to be merged without the last 6 characters, im joining a file, wich is easy to understand. =(A2 & " " & A3) I want A3 without the last 6 digits, I know i have to use the RIGHT function but how so??

View 3 Replies View Related

Concatenate (Join) Cells Across Columns

Feb 9, 2008

I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now.

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A2:A300")
For Each cel In rng
x = x & cel.Value
.Range("A1").Value = x
End With

End Sub

View 2 Replies View Related

Join Text In Two Different Cells Using Concatenate Function?

Nov 19, 2013

I am trying to join text in two different cells using concatenate function / & operator In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?

View 4 Replies View Related

Join Cells & Retain Date Formatting

Nov 30, 2007

I have a number in A1, another number in B1, a Date in C1. In D1, I am referencing all 3 of the other cells: =CellA1 & "-" & CellB1 & "----" & CellC1. Trouble is the date is coming through as a number. Is there a way that I can get the date to show as displayed as opposed to the value of the cell?

View 2 Replies View Related

Join Several Cells & Seperate With Commas And Spaces

Jan 24, 2008

I'm have a list of keywords from google adwords:

cabinet hardware
cabinet hardware knobs
cabinet hardware pulls
kitchen cabinet hardware
amerock cabinet hardware
antique cabinet hardware

These are all in A1, B1, C1, D1, E1, F1

I need to have them look like this: A1 cabinet hardware, cabinet hardware knobs, cabinet hardware pulls, kitchen cabinet hardware, amerock cabinet hardware, antique cabinet hardware. How do I do it? Im a COMPLETE Newbie when it comes to excell but I urgently need to figure this one do I do it?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved