Unconcatenate a column

[NTownend]

SOP57307145_1_1
SOP57307145_1_1
SOP57307145_2_1
SOP57307145_2_1
SOP57307145_2_2
SOP57307145_2_2
SOP57307152_1_1
SOP57307152_1_1
SOP57307152_2_1
SOP57307152_2_1

i have a multiple lines in a column and i want to separate these out, so in the example above i want to get rid of the first 3 characters "SOP" and then separate the next 3 sets of data into 3 columns so i would get

57307145	1	1
57307145	1	1

with the conditions that all three columns could have a different total number of characters in each string i.e

SOP5730714_12_5
SOP573071456_1_15

and so on. text to columns is not workable as i want to automate 1000's of lines
cheers

 

[mohadin]

Hi
Try

Sub test()
    Dim a, b, x
    Dim i&
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
        x = Split(a(i, 1), "_")
        x(0) = Mid(x(0), 4, 255)
        b(i) = x
    Next
    Cells(2, 5).Resize(UBound(b), 3) = Application.Index(b, 0, 0)
End Sub

Or

Sub test()
    Dim a, b, x
    Dim i&
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
    b(i) = Split(Mid(a(i, 1), 4, 255), "_")
    Next
    Cells(2, 5).Resize(UBound(b), 3) = Application.Index(b, 0, 0)
End Sub

 

[Kerryx]

Quick and dirty
find and replace P with _ the use Text to columns option

Book1
	C	D	E
2	57307145	1	1
3	57307145	1	1
4	57307145	2	1
5	57307145	2	1
6	57307145	2	2
7	57307145	2	2
8	57307152	1	1
9	57307152	1	1
10	57307152	2	1
11	57307152	2	1
12	573071456	1	15
Sheet2

 

[Georgiboy]

Maybe TextSplit:

Book1
	A	B	C	D
1	SOP57307145_1_1	57307145	1	1
2	SOP57307145_1_1	57307145	1	1
3	SOP57307145_2_1	57307145	2	1
4	SOP57307145_2_1	57307145	2	1
5	SOP57307145_2_2	57307145	2	2
6	SOP57307145_2_2	57307145	2	2
7	SOP57307152_1_1	57307152	1	1
8	SOP57307152_1_1	57307152	1	1
9	SOP57307152_2_1	57307152	2	1
10	SOP57307152_2_1	57307152	2	1
11	SOP5730714_12_5	5730714	12	5
12	SOP573071456_1_15	573071456	1	15
Sheet1

Cell Formulas
Range	Formula
B1:D12	B1	=VALUE(TEXTSPLIT(SUBSTITUTE(A1,"SOP",""),"_"))
Dynamic array formulas.

 

[NTownend]

Hi
Try

Sub test()
    Dim a, b, x
    Dim i&
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
        x = Split(a(i, 1), "_")
        x(0) = Mid(x(0), 4, 255)
        b(i) = x
    Next
    Cells(2, 5).Resize(UBound(b), 3) = Application.Index(b, 0, 0)
End Sub

Or

Sub test()
    Dim a, b, x
    Dim i&
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
    b(i) = Split(Mid(a(i, 1), 4, 255), "_")
    Next
    Cells(2, 5).Resize(UBound(b), 3) = Application.Index(b, 0, 0)
End Sub

I will give it a go and let you know thanks for prompt reply

 

[NTownend]

Quick and dirty
find and replace P with _ the use Text to columns option
View attachment 73881
View attachment 73882

Book1
	C	D	E
2	57307145	1	1
3	57307145	1	1
4	57307145	2	1
5	57307145	2	1
6	57307145	2	2
7	57307145	2	2
8	57307152	1	1
9	57307152	1	1
10	57307152	2	1
11	57307152	2	1
12	573071456	1	15
Sheet2

Many thanks for prompt reply it works like you say quick and dirty but i want to automate the process and keep orginal data

 

[mohadin]

I will give it a go and let you know thanks for prompt reply

 

[NTownend]

Maybe TextSplit:

Book1
	A	B	C	D
1	SOP57307145_1_1	57307145	1	1
2	SOP57307145_1_1	57307145	1	1
3	SOP57307145_2_1	57307145	2	1
4	SOP57307145_2_1	57307145	2	1
5	SOP57307145_2_2	57307145	2	2
6	SOP57307145_2_2	57307145	2	2
7	SOP57307152_1_1	57307152	1	1
8	SOP57307152_1_1	57307152	1	1
9	SOP57307152_2_1	57307152	2	1
10	SOP57307152_2_1	57307152	2	1
11	SOP5730714_12_5	5730714	12	5
12	SOP573071456_1_15	573071456	1	15
Sheet1

Cell Formulas
Range	Formula
B1:D12	B1	=VALUE(TEXTSPLIT(SUBSTITUTE(A1,"SOP",""),"_"))
Dynamic array formulas.

great thanks for prompt reply, its workable as it keeps orginal data, i have an added problem for this which will follow, shortly .

 

[Kerryx]

No problem plenty of options available

 

[Jimmypop]

Hi there

Can try following formula approach as well...

Book1
	A	B	C	D	E
1	SOP57307145_1_1		57307145	1	1
2	SOP57307145_1_1		57307145	1	1
3	SOP57307145_2_1		57307145	2	1
4	SOP57307145_2_1		57307145	2	1
5	SOP57307145_2_2		57307145	2	2
6	SOP57307145_2_2		57307145	2	2
7	SOP57307152_1_1		57307152	1	1
8	SOP57307152_1_1		57307152	1	1
9	SOP57307152_2_1		57307152	2	1
10	SOP57307152_2_1		57307152	2	1
Sheet2

Cell Formulas
Range	Formula
C1:E10	C1	=TEXTSPLIT(RIGHT(A1,LEN(A1)-3),"_",", ")
Dynamic array formulas.