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.

 

[Peter_SSs]

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

Eric has given you one suggestion for that, with some restrictions and also mentioned the CONCAT function.
My suggestion uses the CONCAT function and also has a restriction. That is that you have a maximum of 9 characters before the slash, though my formula can be adjusted for considerably longer strings before the slash if required.

However, I am also interpreting the following as meaning that there could be more (or less) than 3 capital letters before the slash since you used the words "first three".
Also, although none of your sample data contain capitals after the slash, my formula would exclude those whereas Eric's 'may' include them

I need a formula to get the first three capital letters before the slash ...

Anyway, you could give this a try if you have the CONCAT function in your Excel version.

Excel Workbook
	A	B
1	AbST/1234342	AST
2	Y_sRR/666	YRR
3	B.RE/1234	BRE
4	AB45CDEn/123R	ABC
5	ABCDe/55	ABC
6	SDtr/3e	SD
7	rrrr/234
8	Dr/3DD	D
3 capitals

@Eric
Also note that your use of -78 and <14 causes the [ character to be returned as a capital letter.

 

[Eric W]

@Eric
Also note that your use of -78 and <14 causes the [ character to be returned as a capital letter.

I was aware! There were a few other implicit assumptions in the formula as well, based on the sample data. I figured I'd wait until I got some feedback from the OP as to whether they had CONCAT available before I spent time working on the wrong version.

Here's your formula adapted for unlimited characters before the slash:

=LEFT(CONCAT(IF(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:"&FIND("/",A1&"/")-1)),1)))<13,MID(A1,ROW(INDIRECT("1:"&FIND("/",A1&"/")-1)),1),"")),3)

requiring Control+Shift+Enter.

 

[Peter_SSs]

Here's your formula adapted for unlimited characters before the slash:

=LEFT(CONCAT(IF(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:"&FIND("/",A1&"/")-1)),1)))<13,MID(A1,ROW(INDIRECT("1:"&FIND("/",A1&"/")-1)),1),"")),3)

requiring Control+Shift+Enter.

Hi Eric/hulaspiro

I would choose a slightly different version if there can be a lot of characters before the slash:

=LEFT(CONCAT(IF(ABS(77.5-CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,FIND("/",A1))),1)))<13,MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,FIND("/",A1))),1),"")),3)

Still requiring Control+Shift+Enter confirmation.

- Although a little longer, this version is non-volatile
- I haven't bothered with the -1 after FIND("/"..) since the slash will never be counted as a capital anyway
- I have not included the &"/" in these parts FIND("/",A1&"/") as I would prefer to return an error (or use IFERROR to return something else) rather than "ABC" for an example like "ABCd2" as "ABC" does not meet (to me anyway) the requirement of "the first three capital letters before the slash"