Remove prefix and suffix in data without regex-CodePudding

Goal: If cell A1 begins with "BR" replace "BR" with "-". Remove "L" suffix. If input does not begin with "BR", Remove "L" suffix.

Constraints: Formula can only use the following functions (no Regex):

if
right
left
mid
or
and
len

Possible input value patterns/examples:

BR3L
BR40L
BR500L
BR600L
0L
5L
60L
700L
8000L

What I've figured out so far:
Formula that converts BR prefix values to negatives: if(left(a1,2)="BR","-"&right(a1,len(a1)-2),a1)

Formula that removes "L" suffix: if(right(a1,1)="L",left(a1,len(a1)-1),a1)

CodePudding user response：

try:

=INDEX(IF(A1:A="",,SUBSTITUTE(SUBSTITUTE(A1:A, "BR", "-"), "L", )*1))

CodePudding user response：

overkill, but works too:

=ARRAYFORMULA(IF(LEFT(A1:A, 2)="BR", "-"&RIGHT(
 IF(RIGHT(A1:A, 1)="L", LEFT(A1:A, LEN(A1:A)-1), A1:A), LEN(
 IF(RIGHT(A1:A, 1)="L", LEFT(A1:A, LEN(A1:A)-1), A1:A))-2), 
 IF(RIGHT(A1:A, 1)="L", LEFT(A1:A, LEN(A1:A)-1), A1:A)))