Hello Excel Forum,
I'm Trying to Extracting Specific Text/Word From Inconsistent Text Strings while The Specific Text/Word have a format 4 letter words following with 5-7 Number. from one data cell the Inconsistent Text Strings it can be 1 or more Specific Text/Word need to extract inside separate column if possible.
Here the EX from my data set, while showing the result what i want if possible.
I'm hoping someone here can figure this out. Thank you so much for any help you can offer.
With Ex2016,I would suggest using VBA code.
Are you OK with VBA solution?
Quang PT
Hello, thx for the reply.
I never use VBA, but if that the only solution for now i gladly accept the offer while try to learn how to using VBA Code.
really appreciate it
Hello sir, i'm okay with VBA solution.Originally Posted by bebo021999
and really big thanks for your help
Do you have a list of the 4 letter prefixes (MRTU, FCIU etc) ?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Unfortunately No. But let's just say it can be list, the list would be much maybe more than 50 four letter prefixes. I will try to make the list
for now Here the list i what i know
MRLU
MRTU
SEGU
TCLU
TCNU
TDRU
TGHU
CMAU
FCIU
GESU
SCZU
SMAU
CRSU
DFSU
OOLU
OOCU
MAGU
EGHU
Thank you for the list and it cerainly would be of further help if you can add to it.
It may be possible to find a solution without the list but that certainly depends on how representative your sample file is.
Try to search for "ABCD0000000" with first 4 letters followed by 7 (or down to 5) digits
Try below code
Click on the arrow object, to refresh the result.
PHP Code:Option Explicit
Sub test()
Dim i&, j&, k&, m&, n&, pos&, x&, t&, num
Dim sp, rng, st$, arr(), res(1 To 10000, 1 To 4)
rng = Range("A3").CurrentRegion.Value
ReDim arr(1 To UBound(rng) - 1, 1 To 100)
For i = 2 To UBound(rng)
pos = InStr(1, rng(i, 3), "LIFT ON EMPTY")
st = Left(rng(i, 3), IIf(pos > 1, pos - 1, Len(rng(i, 3)))) & "00"
k = 0
For j = 1 To Len(st) - 11
If isTxt(Mid(st, j, 4)) Then
m = j + 4
For n = 7 To 5 Step -1
num = Mid(st, m, n)
If IsNumeric(num) And num > 9999 Then
k = k + 1: arr(i - 1, k) = j & "|" & n
Exit For
End If
Next
End If
Next
If k > 0 Then
For x = 1 To k
t = t + 1: res(t, 1) = rng(i, 1): res(t, 2) = rng(i, 2): res(t, 3) = rng(i, 3)
sp = Split(arr(i - 1, x), "|")
res(t, 4) = Mid(st, sp(0), 4 + sp(1))
Next
End If
Next
Range("F3:Z10000").ClearContents
Range("F3").Resize(t, 4).Value = res
End Sub
Function isTxt(st As String) As Boolean
Dim i&
For i = 1 To 4
If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(st, i, 1)) = 0 Then
isTxt = False
Exit Function
End If
Next
isTxt = True
End Function
Hello Sir,
Thx for your help, the code is really awesome
But sorry to trouble you again,
i have a question about this "When the description Showing more than One Activity, The container name will be break down or extract by it Activity"
is that possible to do ?
here the ex : for my question in new data set
again and again, i'm very grateful for your help
OK, try again
PHP Code:Option Explicit
Sub test()
Dim i&, j&, k&, m&, n&, pos&, x&, t&, num
Dim sp, rng, st$, arr(), res(1 To 10000, 1 To 4)
Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
rng = Range("A3").CurrentRegion.Value
ReDim arr(1 To UBound(rng) - 1, 1 To 100)
For i = 2 To UBound(rng)
pos = InStr(1, rng(i, 3), "LIFT ON EMPTY")
If Not dic.exists(rng(i, 1)) Then
If pos = 0 Then
st = rng(i, 3) & "00"
Else
st = Left(rng(i, 3), IIf(pos > 1, pos - 1, Len(rng(i, 3)))) & "00"
End If
dic.Add rng(i, 1), st
Else
st = Mid(rng(i, 3), IIf(pos > 1, pos + 13, 1), 255) & "00"
dic(rng(i, 1)) = st
End If
k = 0
For j = 1 To Len(st) - 11
If isTxt(Mid(st, j, 4)) Then
m = j + 4
For n = 7 To 5 Step -1
num = Mid(st, m, n)
If IsNumeric(num) And num > 9999 Then
k = k + 1: arr(i - 1, k) = j & "|" & n
Exit For
End If
Next
End If
Next
If k > 0 Then
For x = 1 To k
t = t + 1: res(t, 1) = rng(i, 1): res(t, 2) = rng(i, 2): res(t, 3) = rng(i, 3)
sp = Split(arr(i - 1, x), "|")
res(t, 4) = Mid(st, sp(0), 4 + sp(1))
Next
End If
Next
Range("F3:Z10000").ClearContents
Range("F3").Resize(t, 4).Value = res
End Sub
Function isTxt(st As String) As Boolean
Dim i&
For i = 1 To 4
If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(st, i, 1)) = 0 Then
isTxt = False
Exit Function
End If
Next
isTxt = True
End Function
Last edited by bebo021999; 04-22-2024 at 09:45 PM.
sorry, It's my bad.
i give you a not clear example of data set.
what i want is, the description can be more than one activity and not limited to specific activity such as "lift on empty". it will break down or extract the container number from each activity.
example (Invoice description have 3 activity) : activity 1 following with 3 cont name, activity 2 following with 2 cont, activity 3 following with 3 cont
the result see my new excel data
this is my new example of excel data
I'm really sorry to trouble you and thanks you for be patient.
Here is a formula-based proposal:
1. Add a column (E) to the source data populated using: =SUM(E2,D3)
2. Populate the output columns for Invoice, Code Item and Description using: =IFERROR(INDEX(A$3:A$5,AGGREGATE(15,6,(ROW(A$3:A$5)-ROW(A$2))/(ROWS(G$3:G3)<=$E$3:$E$5),1)),"")
3. Populate the draft column of container name using: =IF(G3="","",MID(I3,SEARCH("/",SUBSTITUTE(I3,"U","/",COUNTIFS(G$3:G3,G3)))-3,11))
4. Populate the final column of container name using: =IF(RIGHT(K3,1)="M",LEFT(K3,10),K3)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks