Partial row match

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I want to match the first 8 or so letters from the rest of the column and have a formula return a value (itself). Any idea on the formulas that can be used?

Thanks for the help in advance.


3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
not clear what you want

suppose entry in A1 is
asdfghjkqwer

you want the first 8 characters (may be alphabets or numbers) use the formula

=left(A1,8)

read LEFT, RIGHT,MID
in excel help
For example, in the first row, I have ABCDEFGH, and then the second row it is ABCDEFGH123, third row is ABCDEFGH1234 and fourth row is ABCDEFGH12345 etc... I would like to use a formula that would indicate whether or not these rows match (since the first 8 characters match) with any other cells within that column.

I did try the formula: =left(A1,8). This formula will return only the first 8 characters of the specific cell. How can I add the condition that I want to look up whether the first 8 characters matches any of the of the 8 first characters of any other cell within that column?

Angela
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
one pedestrian way of doing it is
hit control+F
in "find what" type the 8 first alphabets(or numbers) of A1
then click find all
you will get below the relevant cells.

another way is through a macro . do you want just know the relevant cells or do anything on these