Split excel cell value into 2 user form text boxes

[Denny57]

I have two scenarios which I would appreciate some help with concerning a unique detail which is used to identify a record

I need to input 2 sets of 3 characters into a single cell and that recall the same into the userform at a later date.

The format needs to be 7 character in "nnn nnn" format and I can current do this by using concatenating two separate text boxes.

Ideally, I would like to format that textbox so that the space after the third character is automatically inserted as the detail is keyed into the textbox (and not when the information is moved into the worksheet so I only need a single textbox. Is this possible?

If not, I have a tried and tested matching process which populates the correct textboxes when I perform a search.
Unfortunately, in this scenario, whilst I can correctly identify the unique item, I cannot seem to find the correct syntax to populate the excel cell value into the two specific user form textboxes.

Dim lastrow
Dim myfind As String

lastrow = Sheets("Passenger").Range("A" & Rows.Count).End(xlUp).Row
myfind = txtUnitNumber1.Value & " " & txtUnitNumber2.Value
For currentrow = 2 To lastrow
If Cells(currentrow, 1).Text = myfind Then
txtUnitNumber1.Value = Cells(Left(currentrow, 1, 3))
txtUnitNumber2.Value = Cells(Right(currentrow, 1, 3))
cboUnitStockType.Value = Cells(currentrow, 2).Value
txtUnitClass.Value = Cells(currentrow, 3).Value

I know the highlighted cells are incorrect and that the code works for all remaining cells (over 20)
txtUnitNumber1 & txtUnitNumber2 are the two textboxes to be populated with the first 3 and last 3 characters of (currentrow, 1)

Can anyone please help?

 

[Yongle]

Ideally, I would like to format that textbox so that the space after the third character is automatically inserted as the detail is keyed into the textbox (and not when the information is moved into the worksheet so I only need a single textbox. Is this possible?

How about this
- inserts the space after user enters 3rd character and prevents user adding any characters after the 7th character

Private Sub TextBox1_Change()
Dim T As String
With TextBox1
    T = .Text
        Select Case Len(T)
            Case 3: .Text = T & " "
            Case 8: .Text = Left(.Text, 7)
        End Select
End With
End Sub

 

[dmt32]

Hi
untested but try

With Cells(currentrow, 3)
    txtUnitNumber1.Value = Split(.Value, " ")(0)
    txtUnitNumber2.Value = Split(.Value, " ")(1)
End With

and see if does what you want when returning values to your textboxes

Dave

 

[Denny57]

Thanks Problem resolved much appreciated