Ignore All The Dashes And Return The Name Only

[Turk182]

Hello All.

I have an Excel sheet for which MOST cells of each row have a dash ONLY (Columns "G" Thru "P"). ONE cell in the row will have a name (either "Bob", "Carol","Ted", or "Alice").
I need an Excel formula that will ignore all the dashes and return the name only (in whichever column it is found) and place the name in column "A" (formula column) of that row.

Is that possible with just formulas?
Thx, Turk182

 

[johnny51981]

Give this a shot:

=IFERROR(INDEX($G2:$P2,MATCH(TRUE,$G2:$P2<>"-",0)),"")

 

[Fluff]

Another option

=FILTER(G2:P2,(G2:P2<>"-")*(G2:P2<>""))

 

[Rick Rothstein]

Since you are using XL365, either of these would also work...

=SUBSTITUTE(CONCAT(G2:P2),"-","")

=CONCAT(IF(G2:P2="-","",G2:P2))

 

[Rick Rothstein]

One more formula that will work in any version of Excel...

=LOOKUP(2,1/(G2:P2<>"-"),G2:P2)

 

[Turk182]

Give this a shot:

=IFERROR(INDEX($G2:$P2,MATCH(TRUE,$G2:$P2<>"-",0)),"")

Thank you.

 

[Turk182]

Another option

=FILTER(G2:P2,(G2:P2<>"-")*(G2:P2<>""))

Thank you.

 

[Turk182]

One more formula that will work in any version of Excel...

=LOOKUP(2,1/(G2:P2<>"-"),G2:P2)

Thank you.

 

[Turk182]

One more formula that will work in any version of Excel...

=LOOKUP(2,1/(G2:P2<>"-"),G2:P2)

Thanks Rick! I appreciate the various approaches/"routes" to achieve the goal, and to learn formulas who is flexibility makes them seem like "languages"!!

 

[Fluff]

Glad we could help & thanks for the feedback.