问题描述:

I want to be able to group a number of values in a column by matching there first n characters where n is the lengh-2

So for 123456 the length is 6 so trying to find a match for the first 4 characters in the cell above.

example below shows Column `A`

with the list of values and the column `B`

is my desired results.

Hopefully this makes sense..

`A B`

RTL1162 |

123456 |

RTL2051 | RTL2051

RTL2052 | RTL2051

RTL2050 | RTL2051

121213 |

RTL9051 | RTL9051

RTL9052 | RTL9051

RTL9050 | RTL9051

I was trying to achieve this using OFFSET but the result is that its only looking at the one 1 row above and I want it to group them all until it doesn't match the first n characters.

`=IF(LEFT(A1, LEN(A1)-2)= (LEFT(OFFSET(A1, -1,0,1,1), LEN(Sheet1!A1)-2)), A1, A2)`

If your list of data is in colA (from **A2** down), you can enter this in B2 and drag down to fill

```
=IF(LEFT(A2,LEN(A2)-2)=LEFT(A1,LEN(A1)-2),B1,A2)
```

In `B1`

enter:

```
=A1
```

In `B2`

enter:

```
=IF(LEFT(A2,LEN(A2)-2)=LEFT(B1,LEN(A2)-2),B1,A2)
```

Fill down.