Any Formulas that would recognize the difference between a persons name and a team names

Question

As you can see there is no spaces between names and team names. I'm able to use

=REGEXEXTRACT, but this formula only divides when it see a uppercase letter.  Is there a formula that would convert the 1st letter to lowercase but not convert any other uppercase. Or is there a formula that would extract the team names only? There are only 20 teams.   

Answer 1

Hi,

I have placed a unique list of team names in column P and applied the following formula in cell S2 (see screenshot).

=LET(pt, LOWER(N2:N21), tm, LOWER(P2:P19), lam, LAMBDA(a,b, SUBSTITUTE(a, b, "")), player, REDUCE(pt, tm, lam), PROPER(HSTACK(player, REDUCE(pt, player, lam))))

Try this formula and let me know if it works for you or not. If it works, then please mark my answer as Valid Answer, as this way other users of this forum also benefit.

IlirU

Answer 2

..but this formula only divides when

..1st letter to lowercase but not convert any other uppercase

You are using the correct formula. You should be able to use that 1 "simple" formula to apply your rule...

Answer 3

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Position of first characters", each Text.PositionOfAny([Names],{" ","-","."})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Position of second capitalised letter", each Text.PositionOfAny([Names],{"A".."Z"},Occurrence.All){1}),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Revised names", each if [Position of second capitalised letter]>[Position of first characters] then Text.Middle([Names],[Position of first characters]+1,30) else [Names]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "Team", each Text.Middle([Revised names],Text.PositionOfAny([Revised names],{"A".."Z"},Occurrence.All){1},20))[[Names],[Team]]
in
    #"Added Custom2"

Answer 4

Since it looks like your data has no spaces between the player names and team names, a reliable approach is to create a list of the 20 team names in a separate range (say X1:X20). Then you can use REGEXEXTRACT in combination with TEXTJOIN and ARRAYFORMULA to extract the team name. Essentially, the formula will look for any of the known team names in the string. For example, if your list of teams is in X1:X20 and your player+team string is in N2, you could use:

=ARRAYFORMULA(TEXTJOIN("", TRUE, IF(REGEXMATCH(N2, X$1:X$20), X$1:X$20, "")))

This will return the team name from the cell because it checks each possible team against the text and outputs the match.

If you instead want to separate the player's name and convert the first letter of the player's name to lowercase while keeping the rest unchanged, you could use:

=LOWER(LEFT(N2,1)) & MID(N2,2,LEN(N2)-LEN(ARRAYFORMULA(TEXTJOIN("", TRUE, IF(REGEXMATCH(N2, X$1:X$20), X$1:X$20, "")))))

This works by taking the first letter, converting it to lowercase, and then appending the rest of the player's name (excluding the team name at the end).

This approach avoids relying on uppercase letters to split the text, which fails in your dataset because both names and teams use uppercase letters internally.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin