# How to Display Repeated Numbers First Digit from B2 to S2 in U2 Cell?

I have a Excel Sheet containing 2 digit numbers in B2 to S2. Now I want to display the First digit of each repeated number ( if they repeat 2 or more times only ) with parenthesis In U2 cell. Ex: If B2, C2, D2 containing numbers 21, 26, 28, I want to display that in U2 cell like 2 (3). is it possible? I have tried some formula, but it showing the repeated numbers along with how many time they repeat. 3 Replies

# Re: How to Display Repeated Numbers First Digit from B2 to S2 in U2 Cell?

@rohithvr Give a try to the following formula-

``=LET(x,LEFT(I2:R2,1),y,UNIQUE(LEFT(I2:R2,1),TRUE),z,MAP(y,LAMBDA(t,SUM(--(x=t)))),TEXTJOIN(", ",1,y&" ("&z&")"))`` # Re: How to Display Repeated Numbers First Digit from B2 to S2 in U2 Cell? in U2:

``````=LET(
FirstDigit, TOCOL(--LEFT(B2:S2,1)),
Duplicates, FILTER(FirstDigit, DROP(FREQUENCY(FirstDigit,FirstDigit) > 1, -1)),
Filtered,   FILTER(FirstDigit, IFNA(XMATCH(FirstDigit,Duplicates),0)),
Frequency,  DROP(FREQUENCY(Filtered,Duplicates), -1),
TEXTJOIN(", ",, Duplicates & "(" & Frequency & ")")
)``````

Alternative:

``````=LET(
FirstDigit,  TOCOL(--LEFT(B2:S2,1)),
Duplicates,  FILTER(FirstDigit, DROP(FREQUENCY(FirstDigit,FirstDigit) > 1, -1)),
JoinDupFreq, LAMBDA(dup, dup & "(" & ROWS(FILTER(FirstDigit,FirstDigit=dup)) & ")"),
REDUCE("",Duplicates,
LAMBDA(seed,dup, TEXTJOIN(", ",,seed, JoinDupFreq(dup)))
)
)``````

Alternative:

``````=LET(
FirstDigit, TOCOL(--LEFT(B2:S2,1)),
Pos,        XMATCH(FirstDigit,FirstDigit),
Frequency,  DROP(FREQUENCY(Pos,Pos),-1),
IsDup,      Frequency > 1,
TEXTJOIN(", ",,FILTER(FirstDigit,IsDup) & "(" & FILTER(Frequency,IsDup) & ")")
)``````

Alternative:

``````=LET(
FirstDigit, --LEFT(B2:S2,1),
Frequency,  TRANSPOSE(DROP(FREQUENCY(FirstDigit,FirstDigit),-1)),
IsDup,      Frequency > 1,
TEXTJOIN(", ",,BYCOL(FILTER(VSTACK(FirstDigit,Frequency),IsDup), LAMBDA(col, TEXTJOIN("(",,col) & ")")))
)``````

# Re: How to Display Repeated Numbers First Digit from B2 to S2 in U2 Cell?

Hi,

I think you can first add a helper column to get "first digit", and then add another column to count the number of occurrences of the "first digit". I attached an example based on your data.