macro to split data to respective columns not working as expected

I have column A which has a set of data which needs to be split to their respective headers column B /C D.

Row1 represents the headers.

In applying 3 different macros with same output,not giving expected results

1 first giving compile error variable not defined.

Please Login or Register  to view this content.

the second macro giving invalid procedure callor argument rin time error 5

Results(R, 2) = Trim(Mid(Data(R, 1), 9, Len(Data(R, 1)) - 16))

Please Login or Register  to view this content.

3 rd macro split data incorrectly

Please Login or Register  to view this content.

Originally Posted by JLGWhiz

sub Split() works for me without txt being declared, But it is good practice to declare all variables with a Dim statement.
Sub SplitTariffData() ran without error for me.
Sub SplitIt() ran withou error for me.

I think the problem is that one or more of the cells in column A has less that the number of characters needed to execute the Mid statement. You can fix that with and If...Then statement

Please Login or Register  to view this content.

You might want to throw an Else in the If...Then statement to give it a default value.

I have tried for as it is and for three as per screenshots ,two first code got run time error and the code gave me inconsistent data.

Mumps and Sintek thanks for solution provided as it works both formula and vba code.

But my approach is much more to vba so that users d' ont mess up with formula as it is a robust database of 35000 rows .

Try the attached

Please Login or Register  to view this content.

Thank you Jindon , Mumps1,alansidman,sintek, JLGWhiz for assistance

All works marvelously.

You are welcome and thanks for the rep.