Formula to Separate Values in Execel

[bjnockle]

I believe there is a way to get around this. Looking for a formula in excel to drop off values from the right when it sees the first dash (-). For example X9-8-06 = X9-8. Second example, V090-88 = V090. If the last values is 3 digits, the formula should not delete the values (stay the same). See sample list below:

Pages                    Magazine (desired outcome)
Z25                      Z25
X9-8-06                  X9-8
V090-88                  V090
ZZ-555                   ZZ-555
Z555-55                  Z555
ZZZZ                     ZZZZ
CX78M                    CX78M
Y67                      Y67
K022                     K022
LL78                     LL78  

Thanks.

[alansidman]

Why does V090-88 become V090 but ZZ-555 stays ZZ-555 and ZZ-55 stays ZZ-55. What is your logic?

[Olly]

I'd use a small UDF:

Please Login or Register  to view this content.

Use as:

Please Login or Register  to view this content.

[bjnockle]

alansidman: ZZ-55 was a typo...I meant to type ZZ-555 = ZZ-555. If the last values is 3 digits, the value should remain the same.

Thanks

[bjnockle]

OllyXLS: Thanks for the code. Is there a formula that can accomplish the same goal with VBA?

[Olly]

I wish you wouldn't edit your initial post / requirement after I have posted a solution - it looks like I ignored the "last 3 digits as number" requirement, when it wasn't actually there. To accommodate that additional requirement, use:

Please Login or Register  to view this content.

I presume your last post means you would like a formula solution WITHOUT using VBA? There probably is. I would use a UDF, though.

[AlKey]

Maybe this

=IF(LEN(A2)<=6,A2,LEFT(A2,LEN(A2)-3))

	A	B
1	Pages	Magazine
2	Z25	Z25
3	X9-8-06	X9-8
4	V090-88	V090
5	ZZ-555	ZZ-555
6	ZZ-55	ZZ-55
7	Z555-55	Z555
8	ZZZZ	ZZZZ
9	CX78M	CX78M
10	Y67	Y67
11	K022	K022
12	LL78	LL78

[Olly]

Here's a formula which does the same as the UDF I posted:

Please Login or Register  to view this content.

Horrible!

[István Hirsch]

Try this formula with 4 functions (enter into B1):

=IFERROR(REPLACE(A1,MIN(SEARCH({"-??#","-?#"},A1&"#-??#-?#")),99,""),A1)

[bebo021999]

Try:
=IF(MID(A2,LEN(A2)-2,1)="-",LEFT(A2,LEN(A2)-3),A2)