How to find and replace the first occurrence of any single text character (Excel)-CodePudding

I need to replace or substitute the first instance of a single text character in an excel row.

current: B01 TEST TEST TEST A W B 0 A
expected result where first "A" that is on its own is replaced with "|": B01 TEST TEST TEST | W B 0 A

The issue is, each row has a character that is segmented on its own, but they are all different (some A, some W, some R, etc). Which function can I use to look for the first instance of a single text character surrounded by spaces?

CodePudding user response：

In Office 365 you could use =AGGREGATE(15,6,FIND(" "&CHAR(SEQUENCE(26,,65))&" ",A19),1)

Older version: =AGGREGATE(15,6,FIND(" "&{"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","Z"}&" ",A19),1)

Edit: better version suggested by Mayukh

=AGGREGATE (15,6,FIND(" "&CHAR(ROW($65:$90))&" ",A19),1)

CodePudding user response：

This uses Office 365 LET and SEQUENCE and is not dependent on it being a capital character. It will replace the first character whether alpha, numeric or special that has a space on either side of it with the |:

=LET(rng,A1,
    sq,SEQUENCE(LEN(rng)-3),
    md,MID(rng,sq,3),
    lft,LEFT(md),
    rt,RIGHT(md),
    REPLACE(rng,MIN(IF((lft=" ")*(rt=" "),sq 1)),1,"|"))