I have a list of numbers and I want to get the common prefix between them.

I've been able to make this array formula that gives me correct output but comparing only 2 numbers (stored as text).

Is there a way to get the longest common prefix between more than 2 strings with an array formula? or for this task only VBA macro could help?

If I have the following 5 values.

The longest common prefix here is

Thanks for any help

I've been able to make this array formula that gives me correct output but comparing only 2 numbers (stored as text).

Code:

```
A1=[COLOR=#0000ff][B]23702[/B][/COLOR]000000
B1=[B][COLOR=#0000ff]23702[/COLOR][/B]999999
C1="=LEFT(A1,SUMPRODUCT((LEFT($A1,COLUMN($A$1:$R$1))=LEFT($B1,COLUMN($A$1:$R$1)))*1))"
Output: [COLOR=#0000ff][B]23702[/B][/COLOR]
```

Is there a way to get the longest common prefix between more than 2 strings with an array formula? or for this task only VBA macro could help?

If I have the following 5 values.

Code:

```
[B][COLOR=#0000ff]7551[/COLOR][/B]1813
[B][COLOR=#0000ff]7551[/COLOR][/B]18
[B][COLOR=#0000ff]7551[/COLOR][/B]184
[COLOR=#0000ff][B]7551[/B][/COLOR]51852
[COLOR=#0000ff][B]7551[/B][/COLOR]581
```

The longest common prefix here is

**7551**but I don´t know how to generalized my formula for more than 2 values.Thanks for any help

Last edited: