find by first two characters

[Guest]

I have a list of rows that contain cells in a varying number of columns. The
columns contain values like: 1-4895, 22-1456, 37-4568 etc. I would like to
copy and separate these cells into columns based on the numbers to the left
of the dash starting at 1, 22, 37 and so on. If the column has a heading of
22 for instance, it would list only the records that contained 22 as the
first numbers of the value. The end result would be that I could filter all
of the rows that had a cell the contained a value starting with 1, or 22 or
37 etc.
I suppose i'm looking for a formula that will copy only values that meet a
specific criteria like an IF statement that selects only the characters
before the dash.

 

[David Biddulph]

You could split by selecting the column, then using Data/ text to columns/
delimited, & putting in the hyphen as a delimiter, but if the data isn't
conveniently in a column then that isn't so easy..
Alternatively you can separate out the characters to the left of the hyphen
by the formula =LEFT(A1,FIND("-",A1)-1), & filter by that.
If you have data in column A and you want to copy into a column with a
particular value in the top row, you could use
=IF(--LEFT(A2,FIND("-",A2)-1)=B$1,A2,"") and copy down and across.

 

[Trevor Shuttleworth]

Let's say the numbers are in A2 to A4:

A2 = 1-4895
A3 = 22-1456
A4 = 37-4568

and the digits before the hyphen are in cells B1 to D1

B1 = 1
C1 = 22
D1 = 37

Then, in cell B2, put the formula:

=IF(LEFT($A2,LEN(B$1))=TEXT(B$1,"@"),$A2,"")


Now drag down and across the cells ... from B2 down to B4 and across to D4

Regards

Trevor