Getting an Excel formula to ignore the last two characters of a field

[SarcasticDwarf]

<b>RESOLVED: SEE BELOW</b>


I have an excel spreadsheet with a couple thousand rows and about 60 columns. What I want to do is get a count of the number of times each value appears, but ignoring the last two digits.

Ex:

121222
121233
111111

That would return 1212 as having appeared twice and 1111 as having appeared once.



Does anyone know of a way to do this? I know I could do a find all, but that would not ignore the last two characters (so 121212 would be the same as 991212).

 

[esun]

Divide by 100 and floor first?

Also looks like Excel has a substring function (haven't used it, but just a Google search of "excel substring" showed some results).

 

[tooltime]

=LEFT(A1,4)
=LEFT(A2,4)
and so on

this gives the first four characters in the first string

 

[SarcasticDwarf]

Looking into it more I think I should be using filters instead of a formula.

I tried to set up the filter as equals 1221** and 1221?? but that returned no output.

 

[tooltime]

if you don't want to use formulas then use Text to Columns, it should be under the Data menu. use a Fixed width to choose where to split the column

i like the this tool. but i think the formula above would work too. if you wanted data the formula was showing to be static you could copy the column with the formula results and do a paste special, values only into another column.

 

[SarcasticDwarf]

Originally posted by: tooltime
if you don't want to use formulas then use Text to Columns, it should be under the Data menu. use a Fixed width to choose where to split the column

Ah, I think that will do it. Thanks.