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
ADVERTISEMENT
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
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
Feb 28, 2014
I have found this code by searching witch is perfect
Code:
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
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
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
29-Mar-09
30-Mar-09
31-Mar-09
1-Apr-09
2-Apr-09
3-Apr-09
4-Apr-09
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
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
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
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
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
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
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
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
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
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
001-1234-1234567-891
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
Oct 27, 2006
For the following formula:
=”TEXT1”&”TEXT2”
The result is:
TEXT1TEXT2
Is there any kind of modifier that I can use to force the output to multiple lines, i.e.:
TEXT1
TEXT2
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
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
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
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
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.
EX.
What I have.
A1 B1
John Smith
Walt Smith
What I want.
A1
John Smith
Walt Smith
View 9 Replies
View Related
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
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
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
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
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
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
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
Next
.Range("A1").Value = x
End With
End Sub
View 2 Replies
View Related
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
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
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 out....how do I do it?
View 4 Replies
View Related
