Inserting zero
 

In excel suppose I have 6Y,2M,3D &6Y,10M,10D &10Y,2M,9D in three different
cell.If I will perform sort on these data in ascending /descending order it
doesn't come properly ,but if I will insert one zero to the left of each
single digit figure it is coming properly i.e. 6Y,2M,3D becomes 06Y,02M,03D
and 6Y,10M,10D becomes 06Y,10M,10D . Hence I want to know how can I insert
zero to each single digit figure by using functions or any other way so that
my large data can be sorted automatically.Because If I will inser zero to
each figure including double digit/three digit then also there will be a
mess.

Thanx .

try something like
=if(len(A1)=2,"0"&A1,A1)

"ananga" wrote:

In excel suppose I have 6Y,2M,3D &6Y,10M,10D &10Y,2M,9D in three different
cell.If I will perform sort on these data in ascending /descending order it
doesn't come properly ,but if I will insert one zero to the left of each
single digit figure it is coming properly i.e. 6Y,2M,3D becomes 06Y,02M,03D
and 6Y,10M,10D becomes 06Y,10M,10D . Hence I want to know how can I insert
zero to each single digit figure by using functions or any other way so that
my large data can be sorted automatically.Because If I will inser zero to
each figure including double digit/three digit then also there will be a
mess.

Thanx .

I'd use a UserDefinedFunction that did the manipulation:

Option Explicit
Function AddZerosYMD(str As String) As String

Dim mySplit As Variant
Dim iCtr As Long
Dim tempStr As String

mySplit = Split97(str, ",")

tempStr = ""
For iCtr = LBound(mySplit) To UBound(mySplit)
tempStr = tempStr & "," & Right("0" & mySplit(iCtr), 3)
Next iCtr

AddZerosYMD = Mid(tempStr, 2)

End Function
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Split was added in xl2k. If you're using xl97, use Tom's split97. If you and
your users are all at xl2k or higher, you can delete that function
completely--and just use:

mysplit = split(str,",")

===
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=addzerosymd(A1)



ananga wrote:

In excel suppose I have 6Y,2M,3D &6Y,10M,10D &10Y,2M,9D in three different
cell.If I will perform sort on these data in ascending /descending order it
doesn't come properly ,but if I will insert one zero to the left of each
single digit figure it is coming properly i.e. 6Y,2M,3D becomes 06Y,02M,03D
and 6Y,10M,10D becomes 06Y,10M,10D . Hence I want to know how can I insert
zero to each single digit figure by using functions or any other way so that
my large data can be sorted automatically.Because If I will inser zero to
each figure including double digit/three digit then also there will be a
mess.

Thanx .

--

Dave Peterson