arraysif-statementgoogle-sheetssubstitutionnested-if
Remove prefix and suffix in data without regex
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)
Solution
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)))
- Convert 2d array rows to a consolidated associative 2d array by grouping on an identifying column
- While destructuring an array, can the same element value be accessed more than once?
- Avoid PHP notice when destructuring array
- Sorting with AlgoBuild. Why do I get an index is out of range error?
- How to use `static` with an array of array in C
- How do I remove random elements in one-go from an array using splice in Perl?
- Sum every cell in nth column with dynamic rows and columns
- ARRAY to XML without any libs in PHP
- VBA - Create empty array
- Reverse a string word by word except the last letter of each word
- change values in array when doing foreach
- C# generic method detect if T is array and then index into the array
- Method to find second highest number in an array in java
- How to store an array of objects in Local Storage?
- How to see if an array includes a nested array with specific values?
- How to fix my modal gallery on how to better improve the succeeding images
- How to send a property from an array of object from a child component to the parent component?
- How does jQuery make it so that its array-like collection objects are displayed like arrays in the browser console?
- Sort array of objects by one property
- Filter/Remove array object based on value, from nested JSON array object in JavaScript
- can't create an array of doubles
- Do Java arrays have a maximum size?
- How to select elements from array in Julia matching predicate?
- Array-size macro that rejects pointers
- Remove Specific Array Element, Equal to String - Swift
- Leetcode - Find the student that will replace the chalk
- Explanation of Python numpy array operations timing behavior
- Python - calculating pdf from a numpy array distribution
- VB.NET Regex.Replace not working (array not updating)
- How to generated N numbers in array that sum of these numbers is equal to 0