VBA Code for split text based on variable length and replace

[Jackyk1994]

Hi everyone, I have a batch of data at column A starting at A3 and I call them “cell.value”

In short, I need to use semicolons to separate my data and replace in the same cell, but I may need to separate multiple times in the same cell.

Raw data example:

PRODUCT0105ABCDE0201L0302AD
PRODUCT0103IPC0201D0303AUS
PRODUCT0106QWERTY0201D0405ASDFG


I want the result to be replaced by:

PRODUCT;0105ABCDE;0201L;0302AD
PRODUCT;0103IPC;0201D;0303AUS
PRODUCT;0106QWERTY;0201D;0405ASDFG

Criteria:

1.“PRODUCT” is always here
2.Each phase is starting with 01-30 which is column name such as product id, no analysis at this stage but fixed 2 length and not all column is mandatory
3.After the 01-30 column name, we have a variable length here, for “0105ABCDE”, 01 is column name no analysis, 05 is the length of the column value which is “ABCDE” and fixed 2 length
4.Pattern is always the same 2 + 2 length fixed + variable length
5.Need to replace at same cell, can’t open new column

Thanks for your help.

 

[Peter_SSs]

I wonder how representative your sample data above is? If that data is what all your data is like, then another way of looking at the criteria is to put a semicolon after every letter that is followed immediately by a digit. If that is the case then you could try this code with a copy of your data.

Sub InsertSemiColons()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "([A-Z])(\d)"
  With Range("A3", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "$1;$2")
    Next i
   .Value = a
  End With
End Sub

Before:

Jackyk1994.xlsm
	A
1
2
3	PRODUCT0105ABCDE0201L0302AD
4	PRODUCT0103IPC0201D0303AUS
5	PRODUCT0106QWERTY0201D0405ASDFG
6
Sheet1

After:

Jackyk1994.xlsm
	A
1
2
3	PRODUCT;0105ABCDE;0201L;0302AD
4	PRODUCT;0103IPC;0201D;0303AUS
5	PRODUCT;0106QWERTY;0201D;0405ASDFG
6
Sheet1

 

[Jackyk1994]

I wonder how representative your sample data above is? If that data is what all your data is like, then another way of looking at the criteria is to put a semicolon after every letter that is followed immediately by a digit. If that is the case then you could try this code with a copy of your data.

Sub InsertSemiColons()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "([A-Z])(\d)"
  With Range("A3", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "$1;$2")
    Next i
   .Value = a
  End With
End Sub

Before:

Jackyk1994.xlsm
	A
1
2
3	PRODUCT0105ABCDE0201L0302AD
4	PRODUCT0103IPC0201D0303AUS
5	PRODUCT0106QWERTY0201D0405ASDFG
6
Sheet1

After:

Jackyk1994.xlsm
	A
1
2
3	PRODUCT;0105ABCDE;0201L;0302AD
4	PRODUCT;0103IPC;0201D;0303AUS
5	PRODUCT;0106QWERTY;0201D;0405ASDFG
6
Sheet1

Thanks for your help.
But unfortunately, my cell values contain different types of format suchas number and text. Can't put a semicolon at the beginning of letter

 

[Jackyk1994]

Hi everyone, I have a batch of data at column A starting at A3 and I call them “cell.value”

In short, I need to use semicolons to separate my data and replace in the same cell, but I may need to separate multiple times in the same cell.

Raw data example:

PRODUCT0105ABCDE0201L0302AD
PRODUCT0103IPC0201D0303AUS
PRODUCT0106QWERTY0201D0405ASDFG


I want the result to be replaced by:

PRODUCT;0105ABCDE;0201L;0302AD
PRODUCT;0103IPC;0201D;0303AUS
PRODUCT;0106QWERTY;0201D;0405ASDFG

Criteria:

1.“PRODUCT” is always here
2.Each phase is starting with 01-30 which is column name such as product id, no analysis at this stage but fixed 2 length and not all column is mandatory
3.After the 01-30 column name, we have a variable length here, for “0105ABCDE”, 01 is column name no analysis, 05 is the length of the column value which is “ABCDE” and fixed 2 length
4.Pattern is always the same 2 + 2 length fixed + variable length
5.Need to replace at same cell, can’t open new column

Thanks for your help.

Add Raw data example:

PRODUCT0105ABCDE0201L0302AD
PRODUCT0103IPC0201D0303AUS
PRODUCT0106QWERTY0201D0405ASDFG
PRODUCT0104ABCD0201E090827072024


I want the result to be replaced by:

PRODUCT;0105ABCDE;0201L;0302AD
PRODUCT;0103IPC;0201D;0303AUS
PRODUCT;0106QWERTY;0201D;0405ASDFG
PRODUCT;0104ABCD;0201E;090827072024

 

[Fuji]

2.Each phase is starting with 01-30 which is column name such as product id, no analysis at this stage but fixed 2 length and not all column is mandatory

Perhaps,

Sub test()
    Dim a, i&
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "([A-Z])((0[1-9]|[1-2][0-9]|30)\d{2})"
            For i = 1 To UBound(a, 1)
                a(i, 1) = .Replace(a(i, 1), "$1;$2")
            Next
        End With
        .Value = a
    End With
End Sub

 

[Peter_SSs]

unfortunately, my cell values contain different types of format suchas number and text. Can't put a semicolon at the beginning of letter

My suggestion does not put a semicolon at the beginning of letter, it puts it between letter and following digit which is what all your examples in post #1 and all your examples in post #4 have.

1. Did you try the code?
2. If my code does not do what you want then please give some sample data and expected results where my code does not work.