Free Microsoft Excel 2013 Quick Reference

Convert number with leading zeros to text using VBA

I receive a report that has personnel numbers starting with zeros. They show in Excel with an apostrophe at the beginning to tell Excel to consider the number as text. I need the personnel number to show as a text value with the leading zeros (to copy and paste to another program). In the worksheet, I can use
=Text (reference cell,"00000000")
to convert the number to text with the leading zeros and without the apostrophe. I want to select a range and use VBA to automate the process but I can't find the correct macro function anywhere.

Thank you.


Post your answer or comment

comments powered by Disqus
Hi, im new to this forum so hello.

Ive been searching around the net for a solution but of yet have not
found one, so hoping that sombody will be able to give me a hand. (im
making a binary to decimal converter for my work at college, i no how
to do the actual code to convert it im just trying to make it neat)

Im trying to split a number with leading zeros. Im using the mid
function to split out the number but for some reson it wont see the
leading zeros

ie spliting 00011110
im using =MID(b4,2,1) which should bring up 0 but its counting the
characters from the first 1.

ive sort of found a temorary solution by converting it to text, but the
imputer has to remeber to put leading zeros on so it makes it 8
characters.

is there any way to make excel add the zeros on (in a text field) to
make it up to 8 characters what ever the imputer puts in.

Any help is much appreciated as im not very good at excell (know more
about access, thats were i got the mid function) so please dont be to
complicated

Thanks in advanced

Helen

--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=471900

Hi, im new to this forum so hello.

Ive been searching around the net for a solution but of yet have not found one, so hoping that sombody will be able to give me a hand. (im making a binary to decimal converter for my work at college, i no how to do the actual code to convert it im just trying to make it neat)

Im trying to split a number with leading zeros. Im using the mid function to split out the number but for some reson it wont see the leading zeros

ie spliting 00011110
im using =MID(b4,2,1) which should bring up 0 but its counting the characters from the first 1.

ive sort of found a temorary solution by converting it to text, but the imputer has to remeber to put leading zeros on so it makes it 8 characters.

is there any way to make excel add the zeros on (in a text field) to make it up to 8 characters what ever the imputer puts in.

Any help is much appreciated as im not very good at excell (know more about access, thats were i got the mid function) so please dont be to complicated

Thanks in advanced

Helen

I need to copy the daa from several cells to one.

I need to maintain specified character length or numbers with leading zeros
in each cell after I combine them into a single cell.

I'm trying to use =C1&C2&C3

Example:

Cell Length Contents
C1 5 00067
C2 3 XY
C3 6 abcde

so I should end up with 00067 XY abcde
after I combine them.

Any help appriceated. thanks.

I am trying to format existing data so that instead of being a set amount of
digits, say 2 for example, it fills out the column based on its width, say 6,
with leading zeros. Example - I have 55122 in a cell that has a width of 6,
and I want it to say 055122. I know that changing the cell format to text and
re-entering the data would fix this problem, but I want a way to change the
data without re-entering it.

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?


	VB:
	
Clipboard.SetText strMyString, vbCFText 
Set xlSheet = xlBook.Worksheets("Sheet1") 
xlSheet.Activate 
xlApp.ActiveWorkbook.ActiveSheet.Range("A2").Select 
xlApp.ActiveWorkbook.ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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 would greatly appreciate any help. Thanks in advance.

Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

I am trying to do a vlookup between 2 spreadsheets and in the one the data is
stored as text and the other as a number with leading zeros (ie. 001) when I
change the format to text it keeps the 001 so I was just wondering if there
is some code to strip the leading zeros out before I reformat the numbers to
text. Your help is appreciated.
TIA
Heather

I have a column that has text that needs to all the same number of characters....with zeros added to the beginning of the different parts of the text.

The text is, for example, 12:5 - 120:15 and needs to be 0012:05 - 0120:15

I can't seem to figure out how to add leading zeros to a part of a cell. Basically the text has to all be ####:## - ####:## I tried using format ...it did not work

Thaks in advance.

Hi,

I receive an excel spreadsheet of daily orders received which I copy to a new workbook (with a macro) and then run another macro that does some formatting and other things ready for the data to be used in a MS Word mail merge for customer welcome letters and order receipts (don't ask why, I just do it!).

Here's the problem. The original sheet has columns E to H formatted as USD currency ([$$-409]#,##0.00) and even though I use the following code to paste the sheet information to the destination new workbook:

  	Code:

Windows("new_orders.xlsm").Activate 'this is the original workbook
With ActiveWorkbook.Sheets("orders")
Lastrow = Sheets("orders").Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:XFD" & Lastrow).Select
Selection.Copy
End With

Windows("new_customers.xlsm").Activate 'this is the destination workbook
ThisWorkbook.Sheets("import_customers").Activate
With ThisWorkbook.Sheets("import_customers")
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E:H").Select
Selection.NumberFormat = "@"
End With

I still end up with the destination formatting for columns E to H being of type currency (£#,##0.00) although it is now in GBP for some unknown reason..... but I need it as text so that it imports correctly to word mail merge.

How can I change the cell format in VBA so that it is text?

There is a second problem. When the value of the cell is an exact pound amount, e.g. £32.00, if I manually convert it to format type text, it changes the cell contents to 32 when I actually want it to be 32.00.

Any ideas on how I can take a currency format and change it to text without losing the post decimal place digits when they are both zeros?

Any help would be greatly appreciated!

Thanks,

Nick

P.S. I'm using Office 2007

When I imported results of a survey containing questions with frequency
distributions ( 1-2, 3-5, 6-10, etc. ) into Excel, Excel converted all the
frequency distributions to dates: 2-Jan, 5-Mar, 10-Jun, etc. How do I convert
these dates back into text? When I reformat the cells containing dates as
text cells the dates are converted to numbers, e.g.:

2-Jan --> 38719
5-Mar --> 38781
10-Jun -->38878

When I attempt to Find/Replace these numbers with the text values I want,
Excel reports that it has made the change but it actually hasn't. I could cut
and paste the values by hand but that would be extremely tedious as there are
hundreds of them scattered throughout the spreadsheet.

I'm not an advanced Excel user. Not familiar with use of functions, but
would appreciate any help...

I have a column in Excel with numeric values such as:

11405
11490
11516
11859

I need a way to convert to a 20 digit number (add 15 leading zeros)......

00000000000000011405

I'm certain there's an easy way for this..........

Thanks in advance.......

When I imported results of a survey containing questions with frequency
distributions ( 1-2, 3-5, 6-10, etc. ) into Excel, Excel converted all the
frequency distributions to dates: 2-Jan, 5-Mar, 10-Jun, etc. How do I convert
these dates back into text? When I reformat the cells containing dates as
text cells the dates are converted to numbers, e.g.:

2-Jan --> 38719
5-Mar --> 38781
10-Jun -->38878

When I attempt to Find/Replace these numbers with the text values I want,
Excel reports that it has made the change but it actually hasn't. I could cut
and paste the values by hand but that would be extremely tedious as there are
hundreds of them scattered throughout the spreadsheet.

I'm not an advanced Excel user. Not familiar with use of functions, but
would appreciate any help...

I need to format a single column of numbers to always have 4 digits. If the cell contents is less than 4 digits, then I need it to concatenate with preceding zeros to bring the total number of digits always to 4.

so...

3205 is ok
905 is not ok....need it to be 0905.

I need to concatentate vs converting to text so the value includes the zeros. These numbers are constantly being input via web query, so a formula in the cell will get wiped out. Possibly need a vba macro to run after the query has finished.

Thanks for any help.

Hi All - I hope someone can help me with this!

I am using the following formula: =CONCATENATE($Page.$B$6;$'Data'.C9;$Page.$B$7)

In the 'Data' range, i have some values with leading zeros. When using the concatenate formula, these leading zeros do not follow through. For example, I get the following:

http://www.site.com/cat/prod/part=70/501

when I want this (note the two leading zeros in 0070):

http://www.site.com/cat/prod/part=0070/501

How can I make this work?

One way, assume the text strings/numbers are in column A starting in A1

=SUBSTITUTE(A1,LEFT(A1,MATCH(TRUE,--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)>0,0)),"")

Enter the formula with ctrl + shift & enter
Copy down

Regards,

Peo Sjoblom

"Cheryl B." wrote:

> I've spent about an hour searching on how to remove leading zeros from a
> column of text. I'm sure there's an easy way to do it ... just haven't found
> it yet. This is a sample of the data and, as you can see, the number of
> leading zeros vary as well as the length of the original and desired text
>
> Original Desired
> 0000000002 2
> 00002309 2309
> 00002477327 2477327
> 00003584 3584
> 0001036 1036
> 00013180620620Y 13180620620Y
>

We have a report in a text format that requires all cells in one column to be filled with leading zeros prior to the existing data. Rather than type in all the zeros, is there a way to insert leading zeros without changing existing data within the cells?

I have tried using the edit/replace replacing blanks with zeros, but this did not work
Jim Partney

I have 3 different values in 3 different cells in excel.
All 3 are custom formated and with a defined number of characters of 5 (00000), 2(00), and 7(0000000), ie 00233,01,and 0040566. How can I concatinate these three values into one text string and retain my leading zero's

I have a CSV file that has a column with leading zeros. When I open the CSV in notepad++ the leading zeros are visible. When I open it with excel I can not see the zeros. Changing the column type to "text" does not solve the problem.

My attempted solution was to rename all my CSVs to file.csv.txt and use the macro recorder to import the data and change the data type to text while importing. This works but I'd like excel to prompt me for a filename instead of hardcoding a file.

There is some sort of syntax error but I'm not sure whats wrong.
    Dim strFileName
    ChDir "C:A"
    strFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If TypeName(strFileName) <> "Boolean" Then
'
        Workbooks.OpenText Filename:=strFileName,
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
        TrailingMinusNumbers:=True
'


Hi.
I have a problem that I want to use VBA to solve but I'm still learning VBA and can't figure it out.
So the problem is:
I have a column of variable length (rows) of dates (example "3/14/2012") which must be altered to the following format: 20120314. Secondly this number must then be converted to text.

I've tried manipulating a recorded macro, but no luck.
Hoping someone with more insight can help me out?
I've attached an example sheet.

Thank you advance.

Hi All,
I have a tab delimited text file which has 100k records.One of the fields inside the text file is Part Number.
The Part Number consists of numbers only which may start with leading zero. The length of the Part Number is also not fixed.

This is how the Part Number look like in the text file:

Part Number
00123
00456
00789
123457
3344578
33445789

This is how the Part Number look like in Excel file:

Part Number
123
456
789
123457
3344578
33445789

There is about 20k of records having leading zero truncated issue when open in excel.It is almost impossible to check 1 by 1 record to make sure that the leading zero is not truncated.

Is there any ways to preserve the leading zero when the text file is open in Excel?

Help Please !!

Greetings, I am a VBA rookie who is just starting to understand the VBA language.

I have a listing of numbers in column A and I am trying to write a macro which will automatically change the number to an 11 digit number with leading zeros if they are already not there. I am able to get the code to work on an individual cell (in this example A1), but I do not know how to make it work for the entire column and stop at the last entry in the column. Any help would be appreciated. Here is the code that I am using, Thanks in advance.

Sub test()

Dim Numbers As Long

Numbers = ActiveSheet.Cells(1, 1).Value
With Cells(1, 1)
.NumberFormat = "@"
.Value = Format(Right("00000000000" & Numbers, 11), "@")
End With
End Sub

I need to achieve a result like this: 12345-0001
However, the user does not want to type the leading zeros.

Help would be much appreciated!!!

I live in New England and our zip codes start with zero. When compiling a
mailing list and then merging in Word, the zeros do not print. Is there a way
to get these zeros to print without having to create a formula (which I have
done). Also, I know that if you start with ' and then type your number the
zero will print. I usually get a mailing list that someone has compiled and
then I am stuck adding the ' to get the zeros to print. There must be an
easier way!!!!!

How to convert number with apostrophe's (ex. 219'2) to decimal format (ex.
219.25)


No luck finding an answer? You could always try Google.