String Function

[hulaspiro]

Here are the sample text:

Input:
1. AbST/1234342
2. Y_sRR/666
3. B.RE/1234

Output:
1. AST
2. YRR
3. BRE

Basically I need a formula to get the first three capital letters before the slash and disregard lowercase and symbols.

 

[lrobbo314]

Welcome to the forum. Give this UDF a shot.

Function ONLYUPPER(s As String) As String
Dim tmp As String
Dim res As String
For i = 1 To Len(s)
    tmp = Mid(s, i, 1)
    If tmp = "/" Then Exit For
    If Asc(tmp) >= 65 And Asc(tmp) <= 90 Then res = res & tmp
Next i
ONLYUPPER = res
End Function

 

[lrobbo314]

Here's another way using regular expressions and no looping.

Function RXOnlyUpper(s As String) As String
Dim SP() As String: SP = Split(s, "/")
With CreateObject("VBScript.RegExp")
    .Pattern = "[^A-Z]"
    .Global = True
    RXOnlyUpper = .Replace(SP(0), "")
End With
End Function

 

[hulaspiro]

Is there a way to edit this via normal excel formula without the use of VBA?

 

[Eric W]

If your formula has at most 5 characters before the slash:

=IF(ABS(CODE(MID(A1,1,1))-78)<14,MID(A1,1,1),"")&IF(ABS(CODE(MID(A1,2,1))-78)<14,MID(A1,2,1),"")&IF(ABS(CODE(MID(A1,3,1))-78)<14,MID(A1,3,1),"")&IF(ABS(CODE(MID(A1,4,1))-78)<14,MID(A1,4,1),"")&IF(ABS(CODE(MID(A1,5,1))-78)<14,MID(A1,5,1),"")

This can be shortened if you have Excel 365 with the CONCAT function.

 

[lrobbo314]

I know you said formula version, but just for kicks here are some Power Query solutions.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList(Text.BeforeDelimiter([Raw],"/")), each if Character.ToNumber(_) >=65 and Character.ToNumber(_) <=90 then _ else null))))
in
    #"Added Custom"

Or you can add the following code to a blank query and then Invoke a custom function on the table.

(txt as text) =>

let
    Source = txt,
    Custom = Text.Combine(List.RemoveNulls(List.Transform(Text.ToList(Text.BeforeDelimiter(Source,"/")), each if Character.ToNumber(_) >=65 and Character.ToNumber(_) <=90 then _ else null)))
in
    Custom

 

[sandy666]

another way with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Input[/td][td=bgcolor:#70AD47]Text[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AbST/1234342[/td][td=bgcolor:#E2EFDA]AST[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Y_sRR/666[/td][td]YRR[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B.RE/1234[/td][td=bgcolor:#E2EFDA]BRE[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2R = Table.AddColumn(Source, "Text", each Text.Combine(List.RemoveItems(Text.ToList([Input]),{"0".."9","a".."z","/","_","."," "})))
in
    T2R[/SIZE]

 

[Rick Rothstein]

Welcome to the forum. Give this UDF a shot.

Function ONLYUPPER(s As String) As String
Dim tmp As String
Dim res As String
For i = 1 To Len(s)
    tmp = Mid(s, i, 1)
    If tmp = "/" Then Exit For
    If Asc(tmp) >= 65 And Asc(tmp) <= 90 Then res = res & tmp
Next i
ONLYUPPER = res
End Function

Here is a more compact way to write your non-RegExp UDF (user defined function)...

Function ONLYUPPER(S As String) As String
  Dim X As Long
  For X = 1 To InStr(S, "/") + 1
    If Mid(S, X, 1) Like "[A-Z]" Then ONLYUPPER = ONLYUPPER & Mid(S, X, 1)
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ONLYUPPER just like it was a built-in Excel function. For example,

=ONLYUPPER(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Phuoc]

For X = 1 To InStr(S, "/") + 1

Should be:
For X = 1 To InStr(S, "/") - 1

 

[Rick Rothstein]

Should be:
For X = 1 To InStr(S, "/") - 1

Yep!