Apr 24 2021 12:48 PM
Apr 25 2021 08:24 AM
Why should 2 be returned from the second example, but not BP from the first example?
Apr 25 2021 08:43 AM
The reason is because I do not want to include the BP in a separate cell. I am looking to put the data
that comes after the BP* and before the end of the ~ in a cell of its own. So the example would be that the script would locate the data between the BP* and the ~ then put it in a blank cell on the sheet. Looking to accomplish the same with the line SN1**2*EA*6~. So in that example anything in between the SN1** and * would go into another cell so this example it would be the 2. Then have the EA go into another blank cell and then the 6 would go into a final cell.
Apr 25 2021 08:50 AM
What is the "rule" behind this? How can we know that you do not want to return BP but you do want to return 2? Both are between ** and *.
Apr 25 2021 09:21 AM
Apr 25 2021 09:24 AM
I don't understand the logic of which parts should be extracted and which ones not.
Apr 25 2021 09:30 AM
sorry for any confusion. I have attached a sample of the data in Column A and the the data in yellow is what I want to extract into the other fields. I hope the visualization helps.
Apr 25 2021 09:58 AM
So now you do NOT want to extract the 6 from A2?
Apr 25 2021 10:08 AM
Apr 25 2021 10:26 AM
You indicate in your sample workbook that the results should be in one row.
Do you want the results for the next group to the right of that, or do you want them on the next row?
Apr 25 2021 10:29 AM
Apr 25 2021 10:44 AM
Try running this macro:
Sub Extract()
Dim r As Long
Dim m As Long
Dim a() As String
Dim t As Long
Dim c As Long
Dim n As Long
Application.ScreenUpdating = False
t = 1
m = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To m
a = Split(Replace(Range("A" & r).Value, "~", ""), "*")
' Optional: write first part to column B
Range("B" & r).Value = a(0)
' Extract parts
Select Case a(0)
Case "LIN"
t = t + 1
c = 3
Cells(t, c).Value = "'" & a(3)
Case "SN1"
c = c + 1
Cells(t, c).Value = "'" & a(2)
c = c + 1
Cells(t, c).Value = "'" & a(3)
Case "PRF"
c = c + 1
Cells(t, c).Value = "'" & a(1)
Case "CLD"
' Skip
Case "REF"
c = c + 1
Cells(t, c).Value = "'" & a(2)
End Select
n = Application.Max(n, c)
Next r
Range("B1").Resize(1, n - 1).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Apr 25 2021 10:57 AM
Apr 25 2021 10:59 AM
Are there blank cells in column A?
Apr 25 2021 11:11 AM