Jumat, 08 Maret 2013

Re: [ExcelVBA] A loop to insert leading zero(s) as string

 

There are MANY ways of doing this.
it's really going to depend on your data.

The other thing is to keep Excel from "converting" the new text to a number.
You'll need to make sure your "destination" cell is formatted as "Text".
 
If all of your data is "pure" numbers, then you can simply change the cell
formatting to:
"0000000000" and you're done!

But if not:

There are two functions that are applicable;
Format() and String()

if some cells contain alpha characters, then something like this should work for
all:

Sub format2()
    Dim R
    For R = 1 To 100
            Cells(R, "C").Value = _
            String(10 - Len(Cells(R, "A").Value), "0") _
            & Cells(R, "A").Value
    Next R
End Sub

Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: Manny <mgarza@fellowes.com>
To: ExcelVBA@yahoogroups.com
Sent: Fri, March 8, 2013 12:59:25 PM
Subject: [ExcelVBA] A loop to insert leading zero(s) as string

 
I want to use a loop to evaluate cells that are formatted as string (this is
same as text right? or NumberAsText).
If the string in the cell is 10 characters long nothing will happen.
If the string in the cell is less than 10 characters I want VBA to insert the
approriate number of "zeroes" to make up the difference thus having a string
that now equals 10 characters.

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___

Tidak ada komentar:

Posting Komentar