# Substitute Characters With Leading Zeros In Multiple Scenarios?

Mar 18, 2014
I have a spreadsheet I work with which contains a formula that needs to take an item ID letter/number combo, substitute the preceeding letter(s) with leading zeros (if the initial numerical characters are fewer than 5) to make the result return a 5-digit number.

The original ID can be in several possible formats (beside each I will post the desired result):

V7684 07684V366 00366V88 00088V60827 60827VS57871 57871VS67 00067

I have the following formula which works like a charm for all except the last example:

=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS","),"V",")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","0"),"V","0"))))

If the first 2 characters are letters ("VS") and there are fewer than 5 numerical characters, the formula results in a 4-digit number, rather than 5. Using the above example, VS67 returns 0067 instead of 00067.

P.S. I just tried

=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS",""),"V","")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","00"),"V","0")))) which only works when there are 3 or fewer numerical characters.

View 6 Replies
ADVERTISEMENT
Apr 2, 2014

I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.

View 10 Replies
View Related
Oct 8, 2012

I have formatted the cells to be '0000' (showing the leading zero) however when my VBA code runs it copies the cell value into a string but when I stop the code running to check the value the leading zero(s) are gone. I can see they are the same on both worksheets in the correct format. I've tried a few others like integer and long but same same.

View 3 Replies
View Related
Nov 21, 2008

I have a CSV file I'm importing into Excell that contains leading zeros in a number field. I cannot get the zeros to display in Excel, but the user of the report needs to see them. Is there a way to dsiplay the leading zeros on a number field?

View 10 Replies
View Related
Oct 22, 2008

How do I keep leading zeros in this code? I am incrementing a value with in a string.

TextBox1.value = "PRO 001"

View 3 Replies
View Related
Jun 28, 2013

I have received an attachment of a .csv file from a client and one of the fields is a tenant ID number that sometimes begins with 1 or more zeros. When I open the attachment it is opening in Excel and dropping the zeros. How can I open that up and keep the zeros. I need those leading zeros.

View 3 Replies
View Related
Jan 3, 2007

I m trying to count strings that have a leading zero with COUNTIF?

Basically, if trying to count "01111", but "01111" does not exist in the data set, and "1111" DOES exist in the data set then COUNTIF returns 1.

View 9 Replies
View Related
Oct 10, 2007

I need a formula that will make a 4 digit number by adding leading zeros. If the number is already 4 digits then don't add leading zeros, do nothing. all numbers will be 1,2,3,4 digits long.

For Example,

if number is 1 then make it 0001

if number is 11 then make it 0011

if number is 111 then make it 0111

if number is 1111 then leave it 1111

View 9 Replies
View Related
Feb 5, 2009

When I save my file as a csv, it drops leading zeros in my zip codes. Formatting the column as text or even zip code only helps when I save it as xls - as soon as I switch it to csv it drops them.

View 9 Replies
View Related
Dec 5, 2006

what i want to do is input a range of numbers into a cell "L12" from 1 to 450

though when in excel i have formated the cell to "number" so when i want number 001 it turns to 1

what i what to do in the macro is after

Dim sNum As Integer

Sheets("FFR").Select

sNum = Range("L12").Value

if the number 1 is in the cell "L12" the number 1 will be in the variable sNum, though i want the number to be 001!

would i have to count the number of numbers in the variable and then say for example if there is only "1" number in the box, use an "add 00" function to get the number in the variable to be 001! I have an idea what to do though no idea where to start!

View 3 Replies
View Related
Dec 29, 2006

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

View 9 Replies
View Related
Jul 19, 2007

i have a header row that i want to transfer to a new spreadsheet. here is my exact setup to make it more clear:

Sheet1 - 001 PM Usage$%

Sheet2 - 022 PM Usage$%

i have dozens of these rows, and i want to set up a auto rename for the header row on sheet 2 to rename itself like this:

Dim the value from header row on Sheet 1 (001), and rename the header on Sheet 2 (022), keeping the remaining contents intact (this is important because i want to use the same function for other headers which varies).

what i am doing now is dumping the value of the first cell into a variable, but as it stands it doesnt store 001, it stores 1... stripping the zeros, which makes it more complicated.

i thought perhaps i should dim the first 3 characters in the header as a string, and rename the first 3 characters in header 2, which i think would be the most efficient, but i dont even know if that is possible.

View 4 Replies
View Related
Jun 13, 2007

I am looking for a way to ensure that all the leading zeros in one column are present without having to format the column cell be cell. Is this possible?

View 12 Replies
View Related
Jun 9, 2009

I have an excel spreadsheet with numbers and the format has to show zeroes at the beginning (e.g. 0011111) It is always going to be a seven digit number. I can format the cells to show the numbers with the leading zeros, but as soon as I save the file as a csv, the leading zeros are lost.

Does anyone know how to the zeros can be retained when the file is saved in csv format?

View 10 Replies
View Related
Jul 8, 2009

I have a whole number in cell C2, but i am formatted it in Cell C3 to always have leading zeros combined with the number in Cell C2, where the result will be 8 digits long. example, my number in cell C2 = 572 but i need it formatted to be 00000572, but if the number was 19 it needs to be formatted to be 00000019 and so on.

View 2 Replies
View Related
Jul 23, 2013

My following code keeps deleting leading zeros. The purpose of this code is to delete leading spaces but leave the zeros.

Code:

lr = Cells(Rows.Count, 1).End(xlUp).row For Each c In ActiveSheet.Range("A2:A" & lr)

If Left(c.Value, 1) = " " Then c.Value = Right(c.Value, Len(c.Value) - 1)

c.Value = c.Value

Next c

View 2 Replies
View Related
Apr 3, 2014

I need either a foumula or macro to add leading zeros to a number based on text in another column

Column A is the original number

Column B is blank and where I want my result to go leaving column A original

Column C is where the text is that the leading zeros will be based on

A1 = 14M3652100

B1 = Blank

C1 = If this column has text "ASP" or "POSMOS" or "ANISON" I need B1 to result in 0014M3652100

View 3 Replies
View Related
Feb 1, 2008

Coworker was sent a spreadsheet with a singe column of loan numbers; some start with zeros, some don't. But every single cell ends with a "^". (Shift key and 6)

i.e. 0087459832^

782360134^

View 9 Replies
View Related
Aug 4, 2006

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?

View 3 Replies
View Related
Sep 29, 2006

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 Related
Feb 27, 2007

I have a web query that returns the following two columns of information:

0011111 0-0-1-1-1-1-1

0110111 0-1-1-0-1-1-1

1111100 1-1-1-1-1-0-0

However, the leading zeros in the first column get dropped off in the Excel worksheet, and the information looks like this:

11111 0-0-1-1-1-1-1

110111 0-1-1-0-1-1-1

1111100 1-1-1-1-1-0-0

This means that my lookups later on don't work correctly. I have tried formatting the cells to text, but this hasn't helped. I also can't add the missing zeros back on, as the length can change.

View 3 Replies
View Related
Mar 28, 2007

I have a web query that connects to our financial system, and downloads cost code values.

However, as some of the values have leading zeros, these get dropped off by Excel.

When I look at the data on the web page, a value could 0080, but when it is imported in to Excel using a web query, it show as 80.

BTW, the values can be different lengths, so I can't just pad the front of the number.

I have formatted the cells to text, and I have set the .PreserveFormatting value = True (see code below)....

View 9 Replies
View Related
Jun 24, 2008

I'm using Excel 2007 and am using a look up formula =isna(match( cell, cell range, 0)) to see whether a number in one column occurs in another column.

I have been doing this process successfully for over a year on Excel 2003 and more recently on Excel 2007. This is until today, when it's stopped doing what I expect - could I have changed a setting?

The correct number format for this sheet should be 0000 (so now '67' will appear as '0067' or '290' will appear as '0290' etc.) So I have applied this Custom Format to my columns: '0000'

After applying the Custom Format to the spreadsheet the numbers appear correctly - 560 now appears as 0560 - BUT in the formula bar they still only appear as the original format - so 560 is STILL 560 rather than 0560 as it should be!

As a result of this, my look up is not looking for the number in the new format, it's still looking for the number in its original format - so a look up for 0560 is actually returning results for anything with 560 in it: 560, 5600, 5601, 56000, 56007 etc.

I need my look up to be looking for 0560 NOT 560.

How do I force the numbers to become 4 digits in the formula bar so that my look up for 0560 only finds a match with a cell containing 0560 and not 560, 5600, 5601, 56000, 56007 etc.

View 3 Replies
View Related
Aug 7, 2008

I have a spreadsheet full of site codes which should be the format:

AA1111

or

A1111

Where A = any alpha character & 1 = any numeric character

unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.

At the moment I manually correct this. Is there a better way to do this?

View 9 Replies
View Related
Sep 15, 2014

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.

View 9 Replies
View Related
Feb 18, 2013

I have a code which displays the values of R2, S2, T2, and U2 in one cell (J2). R2, S2 and U2 are all letters, but T2 is a 2 digit number. If there is a leading zero in T2, then in the display of J2, the leading zero is gone. so if the value of R2 is K, S2 is M, T2 is 01 and U2 is S, then I would want it to display KM01S, but it is only displaying KM1S.

View 1 Replies
View Related
Jul 29, 2014

So quick sample of data :

B74

B74

9

94

1

948

B74

So if I have this data in a column you notice they have different length. Now I want my macro to add leading zeros until the length of all occurrences is 4.

I know for numbers you can do a range.numberformat = "0000".

But this won't work for B74 because (obviously) it will not be recognized as a number.

I know in a formula you can do it with the command TEXT. However I do not want to create extra columns I want something to alter it in place just like the numberformat does but then for numbers and text.

View 14 Replies
View Related
Feb 13, 2009

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.

View 3 Replies
View Related
Mar 14, 2012

In column A I have the following formula =CONCATENATE((YEAR(B1)), " - ", (WEEKNUM(B1,1)))

In column B I have a date.

I need column a to display in YYYY-MM format.

This works great from about mid-March thru the end of the year when the week number is 10 or greater. As an example when the date is 1/31/2012, column A displays as 2012 - 5. For sorting purposes, I need it to display as 2012 - 05.

View 5 Replies
View Related
Jun 18, 2009

I have a column of about 13,000 10 digit numbers. I need to add four zero to the beginning of each row of numbers so I can make it 14 digit numbers. Is there a way to do this without cutting and pasting four zero's for each row of numbers.

View 10 Replies
View Related