Splitting string and distributing to columns with VBA (conditional)

[knutfh]

Hi everyone,

I'm completely lost when it comes to VBA, and now I need a new function/subroutine to use in a bigger VBA-project in Excel.

I have one column that has data corresponding to the column "Original" below.
For each cell in column "Original", I would need to split it as the table shows.

The original must be left untouched. Original can have a varying amount of letters in the beginning, and those starting with SG does not have any dash between the digits.

Original	I	J	K	L
CE 10-001-0002-001	10	001	0002	001
CE 10-001-0005-001	10	001	0005	001
TCE 23-004-3602-001	23	004	3602	001
TCE 24-001-2810-001	24	001	2810	001
TP 60-000-0001-101	60	0000	0001	101
TROLLEY 60-000-0020-000	60	000	0020	000
SG 23004	23	004

Constraints/wishes

• Needs to be called as a subroutine
• Should accept work on column G in ActiveSheet.

I thought about stripping the original for all non numeric characters, but was not desirable.
Then I guess it would be easy to just do a R1C1 reference and get substrings from the original cell.

The following is a solution I'm using in another of the subroutines that is called:

Please Login or Register  to view this content.

Please see the attached file for some example data.

[AndyLitch]

Try this

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[knutfh]

This solution worked perfectly!

Please Login or Register  to view this content.

Would there be much that needs to change if the SG 23000 format should also fill in 0000 (K) and 000 (L) in the remaining columns?

[jindon]

Do you mean

Please Login or Register  to view this content.

[knutfh]

Hi jindon, sorry for the delay in coming back to you. This solved the last part of it.
I really appreciate your efforts!

Best regards,
Knut