Formula for if any word in a cell starts with a specific letter?

[Bakstaj]

Is there a formula to determine if any word in a cell begins with a specific letter? I am trying to build a workbook for people doing a reading challenge. One challenge is to read a book with a word in the title that starts with each letter of the alphabet. I want to automate it so a column for A would get an "X" if any word in the title starts with an "A" and so on through 26 columns.

 

[kweaver]

Would you show a few examples and results using XL2BB please.

 

[Jeffrey Mahoney]

You can use Asterisks in criteria based functions, like this:
=SUMIFS(D9:D13,C9:C13,"A*")
OR
=COUNTIF(C9:C13,"A*")

 

[Bakstaj]

The intent is that for a book of any title length, if any word in the title starts with a letter, an X will appear in that column's cell. The image shows the book "Good Omens." The intent would be for the G and O columns to populate with an "X" automatically

 

[kweaver]

From your clip (not XL2BB) there doesn't seem to be an "X" in either the G or O column. ???

 

[Bakstaj]

From your clip (not XL2BB) there doesn't seem to be an "X" in either the G or O column. ???

That's the point. I can populate them manually, but I am trying to find if there is a formula that would populate them automatically.

 

[kweaver]

What about this:

Sub markletter()
Dim str As String, i As Long
str = Cells(1, 1) & " "

While 0 <> Len(str)
For i = 65 To 90
 If Left(str, 1) = Chr(i) Then
 Cells(2, i - 64) = "X"
 GoTo NLTR
 Else
 End If
Next i
NLTR: str = Right(str, Len(str) - InStr(1, str, " "))
Wend

End Sub

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Good Omens
2							X								X
Sheet7

 

[Jeffrey Mahoney]

Ok, here goes. I created a Lambda function called GetFirstLetters. It returns the first letters of each word that are separated by the Delimter provided. The formula is this:
=LAMBDA(Txt,Del,[Var],[Ltrs],[n],IF(n=0,GetFirstLetters(Txt&Del,Del,(LEN(Txt)-LEN(SUBSTITUTE(Txt,Del,""))),Var,1),IF(n<=Var,GetFirstLetters(MID(Txt,SEARCH(Del,Txt)+LEN(Del),1000),Del,Var,Ltrs&LEFT(Txt,1),n+1),Ltrs&LEFT(MID(Txt,1,SEARCH(Del,Txt)-1),1))))

You need to create a named range called GetFirstLetters and add the formula above
I then use the formula below to test if the letter is in the Lambda result.

Book2
	G
15	Apple Twister Junk Map
16	ATJM
17	K
18	FALSE
Sheet1

Cell Formulas
Range	Formula
G16	G16	=GetFirstLetters(G15," ")
G18	G18	=IF(IFERROR(FIND(G17,GetFirstLetters(G15," ")),FALSE),TRUE,FALSE)

 

[kweaver]

Ok, here goes. I created a Lambda function called GetFirstLetters. It returns the first letters of each word that are separated by the Delimter provided. The formula is this:
=LAMBDA(Txt,Del,[Var],[Ltrs],[n],IF(n=0,GetFirstLetters(Txt&Del,Del,(LEN(Txt)-LEN(SUBSTITUTE(Txt,Del,""))),Var,1),IF(n<=Var,GetFirstLetters(MID(Txt,SEARCH(Del,Txt)+LEN(Del),1000),Del,Var,Ltrs&LEFT(Txt,1),n+1),Ltrs&LEFT(MID(Txt,1,SEARCH(Del,Txt)-1),1))))

You need to create a named range called GetFirstLetters and add the formula above
I then use the formula below to test if the letter is in the Lambda result.

Book2
	G
15	Apple Twister Junk Map
16	ATJM
17	K
18	FALSE
Sheet1

Cell Formulas
Range	Formula
G16	G16	=GetFirstLetters(G15," ")
G18	G18	=IF(IFERROR(FIND(G17,GetFirstLetters(G15," ")),FALSE),TRUE,FALSE)

The OP runs 2016 which doesn't have LAMBDA

 

[Jeffrey Mahoney]

The OP runs 2016 which doesn't have LAMBDA

Well darn