Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Beginning of Year | Excel Worksheet Functions | |||
Add Beginning Text | Excel Discussion (Misc queries) | |||
How do i add " to the beginning of all cells | Excel Discussion (Misc queries) | |||
add "0" to the beginning of each value | Excel Worksheet Functions | |||
Beginning forms | Excel Programming |