Left Function

Laurie Bearden

In the Left function, is there a way to grab just the letters, not another character, such as a dash?

I have a column of numbers that include 2-3 letters, a dash, a number, dash, then 1-3 letters.

Example: XX-10000-WQA, XXX-10000-WQA.

I would like to have a column that just shows the first 2-3 letters, without the dash, if that is possible.

Please, and thank you ☺️

prime_nathaniel

Laurie Bearden

If I understand your request correctly, you will want to use find to find the first instance of "-". Then just return the substring before that position.

=FIND("-", [Primary Column]@row)

=LEFT([Primary Column]@row, [Find the -]@row - 1)

Or combined

=LEFT([Primary Column]@row, FIND("-", [Primary Column]@row) - 1)

Solution explained:

You want to find the - so you use the FIND formula. Once you know where the dash is you now have your number of character parameter for left. You then need to subtract 1 so you don't get the - in your result.

prime_nathaniel

Laurie Bearden

If I understand your request correctly, you will want to use find to find the first instance of "-". Then just return the substring before that position.

=FIND("-", [Primary Column]@row)

=LEFT([Primary Column]@row, [Find the -]@row - 1)

Or combined

=LEFT([Primary Column]@row, FIND("-", [Primary Column]@row) - 1)

Solution explained:

You want to find the - so you use the FIND formula. Once you know where the dash is you now have your number of character parameter for left. You then need to subtract 1 so you don't get the - in your result.

Laurie Bearden

Thank you! I now have another issue…because some rows are empty, in the empty rows, it's coming back with #INVALID VALUE. Can you tell me if I would use ISBLANK or ISNOTBLANK formula?

prime_nathaniel

Laurie Bearden

You have lots of options, technically it is MORE correct to process the input NOT the output of a function so if(isblank(string),"",runStuff). This is beacuse you are "short circuiting" the code and NOT running the formula. This is trivial at small scale but impacts performance massively on 20k sheets as every formula processes via your browser / local resources.

Though most people find it easier to process the output with something like iferrror(runStuff,"")