Hi All,

I have a 3 richtext boxs where I add each line together and output to a textbox, all is working great. But the problem I am seeing is that I paste the contents from a spreadsheet which strips off the leading 0 (zero) from a 9 digit number into the 1st RTB.

What I require is that I need to read each line in the 1st RTB and if the digits are = 8 then prefix them with a 0 (zero).

Any help would be greatly appreciated.

Just to elaborate further... I paste the following:

62820594
63015008
62929642

But I need it to be

062820594
063015008
062929642

regards

If you are going to do a copy and paste then you will get what you get. What is the format of the cells in the spreadsheet (is it an Excel spreadsheet). Numbers don't usuallt display with a leading zero.

If the cells you want to copy can be identified in code I can show you how to copy automatically,

Hi Reverend Jim,

The format that we get it in differs as it comes from a 3rd party, somtimes in plain text and sometimes in an Excel spreadsheet.

There are two types of ID that come to us, one has 7 digits for staff ID's and the other is 9 digits for student ID's. Both set's of ID's could have the prefixed 0 stripped depending on how the 3rd party passes it on to us.

I have a macro based spreadsheet that I use the following code:

If Range("F2").Value = "Staff" Then
Range("A2:A" & MyCount).Select
Selection.NumberFormat = "General"
Else
Range("A2:A" & MyCount).Select
Selection.NumberFormat = "000000000"
End If

Hope this helps

You could store all your data in an array in the text boxes "TextChanged" event procedure. Then use a statement like:

Dim tmp As String
        If (arrayname[i].getLength() = 8) Then
            tmp = arrayname[i]
            arrayname{i] = "0" + tmp
        End If

looping through the array checking each value.

Edited 4 Years Ago by Begginnerdev: n/a

Scary that, I was just playing with the same thing, managed to get everthing into an array and I'm sure that the code you supplied will help in a big way.

Many thanks

okay failing bad here, any hints on getting the array going? if I had any hair left I'd pull it out.

many thanks in advance

Right I can get the data into a RTB but where do I go from here?

Dim arr() As String
        Dim i As Integer

        arr = Split(RTBUserID.Text, vbCrLf)
        For i = 0 To i
            RichTextBox1.Text = (arr(i))
        Next i

I think the problem is more that the spreadsheet is treating the data as numbers if you altered the formating in the spreadsheet to treat it as text it may solve your problem

Although you may need more than one leading zero. Try

Dim i As Integer = 123
Dim s As String = i.ToString.PadLeft(9).Replace(" ", "0")
MsgBox(s)

which pads to 9 digits. You can roll your own function to pad to a variable width. Also, the OP said that the data doesn't always come in a spreadsheet.

Hi Jim,

Ok after messing aroung with the following code:

Dim arr() As String
        Dim i As Integer = RTBUserID.Lines.Length
        Dim s As Integer = i.ToString.PadLeft(9).Replace(" ", "0")

        arr = Split(RTBUserID.Text, vbCrLf)

        For i = 0 To i

            RichTextBox1.Text = s & arr(i)

        Next i

I have this when stepping though..

i = 5

arr(0) = {Length=1} = 62818972 62819154 62999101 62985054 80493029

s = 0

Loops through once then exception occurs with - Index was outside the bounds of the array.

Now if I just set i as an integer without the line count from the RTB the loop goes throug once with the following output in RichTextBox1

062818972
62819154
62999101
62985054
80493029

Starting to get close but not close enough :o(

You can't do a loop for i = 0 to i because you are using the same variable for your loop index as for the end value. To do the padding for each line in the text box try:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

    Dim s As String = ""

    For Each line As String In RichTextBox1.Lines
        s &= ZeroPad(line, 9) & vbCrLf
    Next

    RichTextBox1.Text = s
 
End Sub

Private Function ZeroPad(num As Integer, width As Integer) As String
    Return num.ToString.PadLeft(width).Replace(" ", "0")
End Function

Private Function ZeroPad(str As String, width As Integer) As String
    Return str.PadLeft(width).Replace(" ", "0")
End Function

There are versions of ZeroPad for either string or numeric parameters. If you are wondering why I edited this message three times it is because I have a new kitten that insists on introducing typos by pawing at the keyboard.

Edited 4 Years Ago by Reverend Jim: n/a

wow, worked first time , many thanks Rev.... just a little tweaking and should get it wrapped up today..

Have a great Christmas and again, thanks

This question has already been answered. Start a new discussion instead.