Thread: Multiple words in textbox to different columns

Where's the TextBox?

Alexander;
Let me make sure I understand your needs. You have a textbox on a form with an entry that looks like "excelforum.com Alexander Ceed". You wish for the data to be stored in a table in two different fields. In field one you would have excelforum.com and in field two, Alexander Ceed.

Is this something that you wish to do one time or it will be done on a continuous basis? Is the information in the text box, coming from manual data entry or is it being imported from another source?

Last question: Excel spreadsheet or Access Form?

Alan

presumably a UserForm. this code is for a button on the userform.

Option Explicit

Private Sub CommandButton1_Click()
    Dim vText As Variant
    Dim NextRw As Long

    vText = Split(Me.TextBox1.Value, " ")
    With Sheet1
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = vText(0)
        .Cells(NextRw, 2).Value = vText(1)
    End With
End Sub

Alexander,
If the data is being entered manually, then why don't you create two text boxes that are bound to two fields in your table and enter the appropriate data in each text box? I am confused as to why you would enter all the data into one text box only to split it into two fields.

Alan

Access has the Split Function as well as Excel.

I would urge you to reconsider and not short cut yourself when it comes to fields and controls. If you have them bound and don't need any manipulation the chances of something going awry is mitigated. It helps to keep it simple for users. With multiple entries in a control, what is to prevent the user from entering to many spaces, or not enough spaces to separate properly. Punctuation that is not necessary, or improper capitalization. I personally would go for the extra text boxes. Perhaps set up your form with tabs or pages so it doesn't look so cluttered. If you consider tabs, here is a video that will help.

http://www.datapigtechnologies.com/f...abcontrol.html

Alan