I have a spreadsheet that contains thousands of items.
I am looking to extract all characters until the first colon (:) from RIGHT SIDE only.
COMPAYRNDED:EXANATION:LAEX
:PLUSBOB:G
L34X extract (GL34X)
ANY BRANDED:EXINATION:TEX:PLUS
BOB:GL34P extract (GL34P)
MINATION:LATEX:PLUSBOB:MLX
34/175 extract (MLX34/175)
and do for multiple rows.
I found some code in EE related to that but that didnt make any sense to me. I would appreciate if someone can help me on that. I know it can be done using Mid and Find but I do not know how I can use them in Macro. Will appreciate the help.
==========================
Public Function PullAlpha(ByVal Text As String) As String
Dim Result As String
Dim Index As Long
For Index = 1 To Len(Text)
If Mid(Text, Index, 1) Like "[A-Z]" Then
Result = Result & Mid(Text, Index, 1)
ElseIf Len(Result) > 0 Then
Exit For
End If
Next Index
PullAlpha = IIf(Len(Result) > 0, Result, "BKR")
End Function
Use it in a cell formula like this:
=PullAlpha(A1)
==========================
==========
=======
Thanks.
let me know if any questions.
Start Free Trial
