问题描述:

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.

相关阅读:
Top