Splitting text into columns at space every time data changes using VBA

[Andrew1234]

Hi, I have been heavily researching on how to split information in one cell and have it put into columns using vba.
Any codes I found online either needed me to run the macro every time or they didn't work. I would like it to run every time data is added on this particular sheet.
I have a very basic understanding of vba and simply do not know how to write the coding.

Here are some images showing what I want to achieve.

from these images you can see that I need to split information from column A across columns B - H.
I would like the information to be split straight away as soon as the information comes into column A. I hope this can be done.

Just to make you aware, I also have a simple code for this worksheet that simply move on to the next cell automatically after the information is scanned in using the barcode scanner.

Here is an image of the code used for moving to the next cell.

If someone could help me with this issue it would be much appreciated.
Many thanks for your help in advance!

 

[James006]

Hi,

You could turn on your macro recorder and go through the process once with Data>Text to Columns ...

As a result, an initial macro would be already generated for you ...

 

[Rick Rothstein]

Does this charge event procedure do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Row > 1 Then
    Application.EnableEvents = False
    Target.TextToColumns Target.Offset(, 1), xlDelimited, , True, False, False, False, True, False
    Application.EnableEvents = True
  End If
End Sub

 

[bebo021999]

Is it:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub ' trigger only change in column 1 (column A)
Dim i&, s
s = Split(Target) ' split text string with " " delimiter
For i = 0 To UBound(s) ' loop thru each part
    Target.Offset(, i + 1).Value = s(i)
Next
End Sub

 

[JvdV]

Just for fun, I know you have Excel 2013, but this ms365 solution would auto-update when new data is pasted in column A:

Book1
	A	B	C	D	E	F	G	H
1	Information from scan	Barcode	In/Out	Quantity	First Name	Last Name	Date	Time
2	123 check-in 1 jimmy jones 123 456	123	check-in	1	jimmy	jones	123	456
3	456 check-out 1 jack jones 123 789	456	check-out	1	jack	jones	123	789
Sheet2

Cell Formulas
Range	Formula
B1:H3	B1	=LET(x,{"Barcode","In/Out","Quantity","First Name","Last Name","Date","Time"},IFERROR(REDUCE(x,DROP(TOCOL(A:A,3),1),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),x))
Dynamic array formulas.

 

[Georgiboy]

If you are pasting data in blocks then maybe the below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range, var As Variant
    
    If Target.Column <> 1 Then Exit Sub
    For Each rCell In Target.Cells
        var = Split(rCell.Value)
        rCell.Offset(, 1).Resize(, UBound(var) + 1) = var
    Next rCell
End Sub

 

[Andrew1234]

Does this charge event procedure do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Row > 1 Then
    Application.EnableEvents = False
    Target.TextToColumns Target.Offset(, 1), xlDelimited, , True, False, False, False, True, False
    Application.EnableEvents = True
  End If
End Sub

Thank you for your prompt reply, but unfortunately when I use this code an error appears saying that an ambiguous name detected: Worksheet_Change.

here is an image showing my screen.

This error message was also coming up with a lot of the codes I found online. What should I do?

Many thanks!

 

[Andrew1234]

Is it:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub ' trigger only change in column 1 (column A)
Dim i&, s
s = Split(Target) ' split text string with " " delimiter
For i = 0 To UBound(s) ' loop thru each part
    Target.Offset(, i + 1).Value = s(i)
Next
End Sub

Hello thank you for your reply!

I have tried your code and when data is change in the sheet an error code comes up saying that Ambiguous name detected: Worksheet_Change

this was also the case with a lot of codes i found online.
what do you think i should do?

Thank you for your help!

 

[Georgiboy]

You can't have two subs with the same name, either delete one or change the name of one

 

[Rick Rothstein]

Just for fun, I know you have Excel 2013, but this ms365 solution would auto-update when new data is pasted in column A:

Book1
	A	B	C	D	E	F	G	H
1	Information from scan	Barcode	In/Out	Quantity	First Name	Last Name	Date	Time
2	123 check-in 1 jimmy jones 123 456	123	check-in	1	jimmy	jones	123	456
3	456 check-out 1 jack jones 123 789	456	check-out	1	jack	jones	123	789
Sheet2

Cell Formulas
Range	Formula
B1:H3	B1	=LET(x,{"Barcode","In/Out","Quantity","First Name","Last Name","Date","Time"},IFERROR(REDUCE(x,DROP(TOCOL(A:A,3),1),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),x))
Dynamic array formulas.

@JvdV - Here is another XL365 solution that is somewhat shorter than your formula. Here I am setting a large range (can be as large as you want)...

=VSTACK({"Barcode","In/Out","Quantity","First Name","Last Name","Date","Time"},TEXTSPLIT(TEXTJOIN("*",,A2:A99)," ","*"))