VBA code to split the data as per requirement based on key words

[Wafee]

Hi,
Can someone help we with a code for below scenario.

I have data in A column and need to split it to B, C, D Columns. below is possible formats of data.

1. If it contains data with "AB" then it has to pull the 8 digit alpha numeric (8 numbers + 5 alphabets) ID and paste it in B column cell
2. If it contains data with "FG" then it has to pull the 8 digit alpha numeric ID and paste it in C column cell
3. If it containd data "Ver" then number associated with "Ver" is to be pasted in D column cell. "ver1" and '(ver, 3)" are the only possible formats.
4. If it neither has above three then we can ignore it.
5. if it is blank then ignore it.

thank you in advance.

Column - A	Column B - AB ID	Column C - FG ID	Column D - Version
SINAB76542658 - Ver1	SINAB76542658		1
SINAB76390659 - (Ver, 3)	SINAB76390659		3
SINAB76327645	SINAB76327645
SINFG76542658 - Ver11		SINFG76542658	11
SINFG76542376 - (Ver, 7)		SINFG76542376	7
75638jhm

 

[Peter_SSs]

Is "AB" or "FG" (if they exist) always in positions 4 & 5 of the text like your examples?

Are you sure this needs to be done with vba as I believe it could be done with formulas based on the samples so far?

 

[Wafee]

Y

Is "AB" or "FG" (if they exist) always in positions 4 & 5 of the text like your examples?

Are you sure this needs to be done with vba as I believe it could be done with formulas based on the samples so far?

Yes mate, they will be always in position 4&5. This is needs to in VBA as it is one part of my automation.

 

[Peter_SSs]

they will be always in position 4&5.

Thanks for the clarification. Try this with a copy of your data.

Sub SplitToColumns()
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim s As String
 
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 3)
  For i = 1 To UBound(a)
    s = a(i, 1)
    Select Case True
      Case Mid(s, 4, 2) = "AB"
        b(i, 1) = Left(s, 13)
      Case Mid(s, 4, 2) = "FG"
        b(i, 2) = Left(s, 13)
    End Select
    If InStr(14, s, "ver", vbTextCompare) > 0 Then b(i, 3) = StrReverse(Mid(Val(StrReverse(Replace(s, ")", "") & 1)), 2))
  Next i
  Range("B2:D2").Resize(UBound(b)).Value = b
End Sub

My sample data and results:

Wafee.xlsm
	A	B	C	D
1	Column - A	Column B - AB ID	Column C - FG ID	Column D - Version
2	SINAB76542658 - Ver1	SINAB76542658		1
3	SINAB76390659 - (Ver, 3)	SINAB76390659		3
4	SINAB76327645	SINAB76327645
5	SINFG76542658 - Ver11		SINFG76542658	11
6	SINFG76542376 - (Ver, 7)		SINFG76542376	7
7	75638jhm
Sheet1

 

[Wafee]

Thanks for the clarification. Try this with a copy of your data.

Sub SplitToColumns()
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim s As String

  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 3)
  For i = 1 To UBound(a)
    s = a(i, 1)
    Select Case True
      Case Mid(s, 4, 2) = "AB"
        b(i, 1) = Left(s, 13)
      Case Mid(s, 4, 2) = "FG"
        b(i, 2) = Left(s, 13)
    End Select
    If InStr(14, s, "ver", vbTextCompare) > 0 Then b(i, 3) = StrReverse(Mid(Val(StrReverse(Replace(s, ")", "") & 1)), 2))
  Next i
  Range("B2:D2").Resize(UBound(b)).Value = b
End Sub

My sample data and results:

Wafee.xlsm
	A	B	C	D
1	Column - A	Column B - AB ID	Column C - FG ID	Column D - Version
2	SINAB76542658 - Ver1	SINAB76542658		1
3	SINAB76390659 - (Ver, 3)	SINAB76390659		3
4	SINAB76327645	SINAB76327645
5	SINFG76542658 - Ver11		SINFG76542658	11
6	SINFG76542376 - (Ver, 7)		SINFG76542376	7
7	75638jhm
Sheet1

That works perfectly mate. Thank you Genius

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.