0

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.

Jimmy.L
  • 25
  • 5
  • Could you please add some more information or examples about the input and the desired output. – Storax Nov 04 '19 at 11:12
  • Can you please have a look at this link: https://stackoverflow.com/help/how-to-ask As stated above we would need to know the expected result. Thanks. – GBouffard Nov 04 '19 at 11:47

1 Answers1

1

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
Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20