How to remove REGEXEXTRACT #N/A error and ignore special characters in Google Sheet?

user_1360601 0
Viewed 461

Let's say I have column A:A containing titles. In another column I want to show the first letter of title, ignoring special characters, like so:

Title Chapter
(A) Title A
Title T

This is an array formula put on B1 I made so far, but not working properly as my example above:

=ARRAYFORMULA(if(row(A:A)=1,"Chapter",REGEXEXTRACT(A:A,".")))

It contains an error message #N/A on every row if column A doesn't have text. It also shows a special character if it begins with. This is how it currently looks:

Title Chapter
(A) Title (
Title T
#N/A
#N/A

Any ways to fix this?

google-sheets-formula google-sheets
2 Answers

See if this helps

={"Chapter"; ArrayFormula(left(regexreplace(A2:A, "[^A-Za-z]*",), 1))}

Example

user_38894230

Also try the simpler

=ArrayFormula({"Title "; 
        IFERROR(REGEXEXTRACT(B2:B,"\w"),)})

(do adjust ranges to your needs)

enter image description here

user_15277800
Related