I cant seem to figure out how to concatenate data from two cells into one cell and retain trailing zeros. If Cell A1 has ".0000" and cell A2 has ".0005" then I want cell A3 to show:
".0000-.0005".
I get "0-.0005" on my attempts. Alos, how do I make the value an actual number and not a formula?
I asked this along time ago and received a worksheet formula which suited my needs then. http://www.excelforum.com/excel-work...trailing+zeros
Now I need this in a macro. Below is my current concatenating code.
Now I have a decimal column and I would like to remove all of the trailing zeros. It is using the number format. How do I accomplish this?
View 10 Replies View RelatedI'm attempting to create a spreadsheet to calculate various things at my work, however I'm running into a problem that I can't solve. In one cell, I have a formula that returns an value to two significant figures. The number of decimal points that this value has is then used at various points throughout the sheet to truncate/round/etc. However, if the first value has a trailing zero as the second significant figure (ex. 0.20), that trailing zero is dropped. This creates problems since downstream calculations are then truncating to an incorrect number of decimal points. How to retain this trailing zero IF the formula results in one?
View 4 Replies View RelatedI have seen a few posts that are close to this but not quite right. I have a situation where I need to do "=LEN..." formula that counts trailing zeros at the end of numbers (meaning zeros that are displayed at the end of a number but do not appear in the actual cell value).
example:
Cell E19 contains the actual value 4773.52, but it is displayed in currency format with FOUR digits so it displays as $4,773.5200.
The formula I am using is =LEN(MID(E19,FIND(".",E19)+1,4)). The result is 2, which means it does not include the last two trailing zeros in the LEN count.
Is there anyway to get it to include the trailing zeros so the result equals 4? Either w/formula, macro, or UDF?
Is weird I know, but I need to know how many decimal places (displayed, not actual) for many numbers on many sheets that I do not create myself.
I need to never have a trailing zero be displayed but still have decimals displayed (we can use up to 3 digits for the example) Here's a list of test cases
Value______Display
1.234567___1.235
1.234______1.234
1.23_______1.23
1.230______1.23
1.2________1.2
1.200______1.2
1.0________1
1.000______1
1.020______1.02
1.0002_____1
0__________0
0.001______0.001
0.0001_____0
The only time I want to see a 0 is when the answer is 0 (or rounded to 0) or when 0 is required in the middle. This is for patient safety if you are curious. 2.0 when printed out or written by hand can be mistakenly read as "20" in the heat of things, thus 2.0 should be written/printed as "2" (no decimal on the end) not as "2." even, just "2"
I can't seem to get the Concatenate function to work in my macro when there is a combination of numbers and text. It works fine is there are no alpha characters. I am using this macro to format all of the data in column "A" to have 5 characters and be text. These values will later be used in formulas and Pivot Tables.
Sub Macro2()
Dim sinlen As String
Dim sinformat As Variant
Dim lastcell As Variant
Range("b1").Select
Selection.EntireColumn.Select
Selection.Insert Shift:=xlLeft
Range("a1").Select
Do
ActiveCell.Value = "'" & ActiveCell.Value ......
I have ID numbers that sometimes starts with one or more zeros and when i try to paste a string containing ID numbers that starts with a zero, excel converts it to a number thereby deleting the leading zero(s). Is there any way to force excel to keep the leading zero when i paste from the clipboard?
Clipboard.SetText strMyString, vbCFText
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Activate
xlApp. ActiveWorkbook.ActiveSheet. Range("A2").Select
xlApp.ActiveWorkbook.ActiveSheet.Paste
I have tried setting the format for a column as text using xlSheet.Columns("D").NumberFormat = "text" before pasting to try to force excel to keep the leading zero but the ID# comes out unreadable as "############". I have also tried formatting as "General" and custom formatting the column before pasting but have been unsuccessful thus far. I've seen other programs accomplish pasting numbers with leading zeros but how do you do this?
I am just basically sorting through a huge dataset and grabbing unique codes and storing them in an array for later use. Some of the codes are numeric and some are alphanumeric therefore I am storing them as strings. However, when a code is for example 000578 - once a recall this from the array it has become 578. Is there anyway to preserve the entire code, i.e. make the array recall 000578.
View 6 Replies View RelatedI have 4 columns (and a couple of other text columns) that are formatted as decimal with 6 dicimal points.
I need to create a text file with lines that are 80 bytes each from the spreadsheet - no spaces inbetween each of the characters.
The issue I am having is trying to format the 4 decimal point columns to be fixed numbers and retain the leading or trailing zeros to do a concatenation with the other columns.
Just an example of some what some of the values look like on my spreadsheet:
.123456 | .123450 | .012345 | .012340
Essentially when I'm all said and done, I would like the row to look like "text123456123450012345012340text"
I tried to format 4 new columns as general and use a "=right(cell,6)" and that worked pretty well for the cells that have leading zeros, but for the cells that have trailing zeros, it doesn't seem to pick up the zero. I tried to use a "=mid(cell,2,6)" but that didn't work either.
I had been trying to calculate cells that contain numbers with leading zeros.
I can't convert the cells to TEXT because Formulas don't work in TEXT Cells.
I cant use Custom Formatting because the rows may contain different number of leading zeros.
Also Is there a way that I can trim the numbers from Col A and put them in C?
Currently I don't like having to change the RIGHT(G2, 3) to RIGHT(G2, 4)
if the number is smaller than the number of char. option.
I have a macros which copies a column of cells to a different spreadsheet and does a few other complex tasks with the data. Some of the data are reference numbers such as 00012345 or 001 which is changed to 12345 and 1 after being copied. What macros could I use to copy this data as is without it being changed?
View 6 Replies View RelatedI want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
i cant do this from importing the data, so instead i have to try and fix it with code. I get a sheet of data from an external source that removes the 0's in the front of the number. Its only a 4 digit number so 21 would be 0021. Now i have been able to trap the data i think i would need. i created a =len() statement to tell me how many numbers each cell has. here is the code i think how it would work.. i just need a little help with filling in the blanks. I would assume its something to the lines of " 00+string value if it was 2 numbers needed.
B2 has the Len values (4,3,2,1 etc) and A1 is the original string that has the values.
So i want the code to add 1 2 or 3 zeros to the front of the number based on the len value.
Range("B2").Select
While ActiveCell.Value <> ""
ActiveCell.Offset(0, 0).Select
Select Case ActiveCell.Value
Case Is = "3"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 1).Select
Case Is = "2"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 1).Select......................
I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.
I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.
When I use this formula I get 353942. How can I retain these zeros?
I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....
View 6 Replies View RelatedI 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.
i need to connect some cells, but have to check to make sure cells dont have zeros in them first
example m13 m14 m15 m16
................5......10......0........15
in cell q18 i want it to show this 5/10/15, dropping the zero cell
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
View 2 Replies View Relatedwith the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?
Vendor’s Name
Jul 09
Aug 09
Sep 09
Oct 09
Nov 09
Dec 09
Jan 10
Feb 10
Mar 10
Apr 10
May 10
June 10
1
Vendor1
20
5
15
3
0
0
0
0
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
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), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
how to set the format of a cell such that it will automatically add trailing blanks to the data entered into that cell while fixing the number of characters that could be entered into that cell?
View 3 Replies View RelatedI have a column of data in which each cell contains a line of text. Each line of text has spaces after the letters end and there doesn't seem to be a consistent number of trailing spaces.
I can clean up the data using the TRIM function but is it possible to use the TEXT TO COLUMNS functionality?
I would like to use the following code to produce a message with two numbers in it, both showing an exact golf handicap to one decimal point. If a number is exactly 6 I want it to show as 6.0.
All works well for the number I'm collecting from the user and storing in newh. But I can't retain/produce the trailing zero from oldh which is formatted in the spreadsheet as Custom 0.0.
I need a macro to do a comparison between two spreadsheets, and one part of it is throwing me, as I need to compare apples to oranges. Here's the situation:
Sheet A has the data noted like this:
PAC
11000
11100
11500, etc.
All data should have 5 digits, and my numbers range from 11000 to 87028, though quite a few are skipped.
Sheet B has data like this:
PAC
110
110-CBS
11000
11100-ING
112
11500-L
How can I change sheet B to:
1) Drop off the dash and anything following?
2) Add trailing zeroes to make sure all numbers are 5 characters in length?
What the quickest way to get rid of trailing spaces from a column of imported data ?
View 12 Replies View RelatedI have a set of data with trailing Y in certain cells.
Instead of the replace function in Excel, is there another way of removing the "Y" in that cell and storing it another cell while keeping the numbers before the Y.
i've created another column to determine which cells has Y as the right most character by using =IF(RIGHT(F2,1)="Y","Y","")
So for example, I want to remove the Y from say Cell A2 and store it in say cell A3 while still keeping the value 8624.46 in A2
Cell A2 = 8624.46Y
I have an excel file with two sheets . One containing the updated prices with its code ( Sheet1) and the other containing the old prices with the same codes (sheet2). Now after several trials to copy the new prices from sheet 1 to sheet 2 with check if the code is same . ( I couldn't )
So how to get rid of the 20 spaces from Sheet1 column A.