I am using text-to-column function to split a string which is taken from a input box. How do I define the text format for the split text? At the moment, the text "3101/09/1" will be automatically changed to a Date format "01/09/3101".
I have tried to use the FieldInfo function as below.
e.g. if I only type 2 segments in the input box: "3101/10/1 2910/09/2" FieldInfo:=Array(Array(0, 2), Array(1, 2)) This works fine.
However, if I type in 3 segments in the input box: "3101/10/1 2910/09/2 2910/09/3" FieldInfo:=Array(Array(0, 2), Array(1, 2), Array(2, 2)) This will not work. It gives something like:
¦ 3 ¦ 1 ¦ 01/10/1 ¦ 02/09/2910 ¦ 03/09/2910 ¦
The codes are:
Cells(1, 1).NumberFormat = "@"
Cells(1, 1) = inputBox.Text
Range("A1").TextToColumns Destination:=Range("B1"), Space:=True
Final Edit the solution:
Dim r As Excel.Range
Dim s() As String
s = Split(inputBox.Text, Chr(32))
Set r = Range("B1").Resize(1, UBound(s) + 1)
r.Value = s
Thanks all.
Something like this using the split function may be of help
Dim r As Excel.Range
Dim s() As String
s = Split("3101/10/1 2910/09/2 2910/09/3", Chr(32))
Set r = Range("k1").Resize(1, UBound(s) + 1)
r.value = s