Condensing Data into a Range

Jasmyn1565 

https://club.excelhome.net/forum.php?mod=viewthread&tid=1670826&fromguid=hot&extra=&mobile&_dsign=eef56206

select * from 带字母序号离散转连续;
create temp table aa2 as
select *,regexp2('(\D+)(\d+)',序号,1) 前缀,regexp2('(\D+)(\d+)',序号,2) 原始 from 带字母序号离散转连续 order by regexp2('(\D+)(\d+)',序号,2) ;
create temp table aa3 as select *,原始-row_number() over (partition by 前缀) 差 from aa2;
create temp table aa as
select *,iif((select count(*) from aa3 a where a.前缀=b.前缀 and a.差=b.差)>2,1,rowid) 计数 from aa3 b;
create temp table bb as select 原始,差,iif( (max(原始)-min(原始)>1),前缀||min(原始)||'-'||前缀||max(原始),前缀||原始) 新序号,前缀 from aa group by 前缀,差,计数;
select group_concat(新序号) from bb group by 前缀;

Jasmyn1565 I have a few questions...

1. What version of Excel are you using?
2. Do the numbers always start from 1 and include all numbers from 1 to the maximum occurrence for each name?
3. What exactly do you mean by "all the data is in the same cell"?

If you have Excel for MS365, one possibility (based on my assumptions for question 2 & 3) could be:

=LET(
    rng, A2:A7,
    unq, UNIQUE(TEXTBEFORE(rng, " ")),
    qty, COUNTIF(rng, unq & "*"),
    unq & " 1" & IF(qty>1, "-" & qty, "")
)

Sample Results

However, I'm not entirely sure my interpretation of your described dataset was accurate.