Keeping 0 at beginning
 

I have a amcro that splits up the info in a cell by using text to columns.
This works fine and everything comes out right execpt for one of the data
sets has a leading 0 that disappers when in the split up form. Let me give
you an exaple.

I have a cell with the follwoing string: "ACGB 4.75 0311". This is then
split up so that it in 3 different cells shows: "ACGB" (e.g. in cell b1) and
"4.75" (e.g. in cell b2 and "311" (in e.g. cell b3). Notice how the leadÃ*ng 0
is gone. In my code I set the format of the cells to text but it does not
work. Con you please help me fix this problem? I know it does not sound very
important but it makes my program not very useful. My code is:

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
r = r + 1
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
rngSecID.Offset(r, lngColumnRight).Select
Selection.NumberFormat = "@"
rngSecID.Offset(r, 0).Select
Selection.TextToColumns Destination:=rngSecID.Offset(r,
lngColumnRight), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False,
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1),
Array(3, 1)), TrailingMinusNumbers:=True
End If
Loop

Please any help appreciated! Thanks alot!

Keeping 0 at beginning
 

One way. Before you ran your code, make sure all the numeric block
starting with 0 are prefixed with an apostrophe('). Eg "ACGB 4.75 0311
becomes "ACGB 4.75 '0311". The following code can achieve that:


Sub Primer()
Dim c As Range
Dim i As Integer

For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row
'assuming data is inCol A
For i = 1 To Len(c)
If Mid(c, i, 1) = Chr(32) And Mid(c, i + 1, 1) = "0" Then
k = k & Mid(c, i, 1) & Chr(39)
Else
k = k & Mid(c, i, 1)
End If
Next
c.Value = k
k = ""
Next
End Su

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=57379

Keeping 0 at beginning
 

Thanks! My problem is that the data is retrieved from another program and is
too much data to change by hand. Might come up with something though based on
your idea. Thanks very much!

"Myles" skrev:


One way. Before you ran your code, make sure all the numeric blocks
starting with 0 are prefixed with an apostrophe('). Eg "ACGB 4.75 0311"
becomes "ACGB 4.75 '0311". The following code can achieve that:


Sub Primer()
Dim c As Range
Dim i As Integer

For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
'assuming data is inCol A
For i = 1 To Len(c)
If Mid(c, i, 1) = Chr(32) And Mid(c, i + 1, 1) = "0" Then
k = k & Mid(c, i, 1) & Chr(39)
Else
k = k & Mid(c, i, 1)
End If
Next
c.Value = k
k = ""
Next
End Sub


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=573795

Keeping 0 at beginning
 

My problem is that the data...

There is no problem. Simply embed your code thus using the cal
method:

Sub TextToColumn()
Dim c As Range
Dim i As Integer

For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row
'assuming data is inCol A
For i = 1 To Len(c)
If Mid(c, i, 1) = Chr(32) And Mid(c, i + 1, 1) = "0" Then
k = k & Mid(c, i, 1) & Chr(39)
Else
k = k & Mid(c, i, 1)
End If
Next
c.Value = k
k = ""
Next
CALL YOURCODENAMEASAPPLICABL
End Su

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=57379