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!

 

[Andrew1234]

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.

Hi thank you for your help!

I have pasted your formula into cell B1 and unfortunately it is showing this error seen below.
Have I done this correct?

Thanks

 

[Rick Rothstein]

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

You added my Change event procedure to the one you already had... you can only have one Change event procedure, so either remark out or delete the one you already had.

 

[Andrew1234]

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

Ok Thank you!

i will try the above formulas again and change "Worksheet_Change" to "Text_Split"

Hopefully this will work!

 

[JvdV]

@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)," ","*"))

@Rick Rothstein yeah I used REDUCE() to overcome the limits of TEXTJOIN()'s 32767 characters (cell's limits) which theoretically can pose an issue for OP. Also, TOCOL(A:A,3) would make for more dynamic use I believe, filtering out empty cells.

 

[Andrew1234]

You added my Change event procedure to the one you already had... you can only have one Change event procedure, so either remark out or delete the one you already had.

Hi,
unfortunately I must have the first worksheet change code, As if I didn't have it, excel wouldn't know to move onto the next cell and all of the the data I wirelessly scan into the sheet would be in one cell. And therefore very difficult to get the information split.

what i will be using this for is a store inventory system. so i will select this work sheet and the next available cell in column A. Then go out into the store and scan items. as i will not be right beside the computer i would need excel to be able to automatically go down to the next cell and also split the information into the various columns. i will then create more work sheets which glean the information from this sheet.

Hopefully there is a solution and a way to work around this issue.

Again, many thanks for your help

 

[Andrew1234]

@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)," ","*"))

Hi,
Unfortunately I cannot get the these formulas to work.

All i have done is copy and paste your formulas into cell B1 and it shows an error.

Is this what i am meant to do?

thanks

 

[James006]

Hi,
Unfortunately I cannot get the these formulas to work.

All i have done is copy and paste your formulas into cell B1 and it shows an error.

Is this what i am meant to do?

thanks

You are using Excel 2013 ... as a consequence, you cannot use these functions ...

 

[Andrew1234]

You are using Excel 2013 ... as a consequence, you cannot use these functions ...

Ok thank you for letting me know as i was tying to make this work....

I have just tried your first suggestion of recording a macro. And yes it does work, but i have to click run macro every time. is there a way that i can set thuis to automatically run every time a new line of data is pasted into column A?

Also bare in mind that i need this code in to force excel to move down one cell after that data is scanned in...

Private Sub Worksheet_change(ByVal Target As Range)
Dim ChangedCell As Range
Set ChangedCell = Range(Target.Address)

'Select the next cell
ChangedCell.Offset(1, 0).Select
End Sub

This seems to be a problem as codes i have been advidsed to use above have the same name.

Is there a way that i can keep my cell auto jump code and also have and also one for splitting the text?

Many thanks

 

[Georgiboy]

Maybe try it this way, it will drop down a cell each time something is scanned - ready for the next scan:

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

Sometimes it is about combining the functionality of two worksheet_change codes

 

[James006]

You can test following

Private Sub Worksheet_Change(ByVal Target As Range)
' In Sheet Module
Dim ChangedCell As Range
Set ChangedCell = Range(Target.Address)
Application.Run ("TextinCols")
'Select the next cell
ChangedCell.Offset(1, 0).Select
End Sub

Sub TextinCols()
' In Standard Module - Rick's macro
 ActiveCell.TextToColumns ActiveCell.Offset(, 1), xlDelimited, , True, False, False, False, True, False
End Sub