IF Statement with multiple or conditions

[aquad6]

Hi There!
I am trying to write a formula that allows me to place a dash before the last character in a cell text if it is an A,B,C etc. For example, if I have a sku that reads JH004A and I would like a formula that will populate another cell with JH004-A. IF it doesn't contain A,B,C etc. I want the other cell to read False. So far this formula =IF(AND(RIGHT(A2,1)="B"),LEFT(A2,LEN(A2)-1)&"-"&RIGHT(A2,1)) works but only if the end character is a "B", I can't figure out had to make it work if the end character is an A, or C. Any help would be greatly appreciated!! Thank you!

 

[kevin9999]

Book1
	A	B
2	AAAB	AAA-B
3	AAAA	AAA-A
4	AAAC	AAA-C
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF(AND(OR(RIGHT(A2,1)={"A","B","C"})),LEFT(A2,LEN(A2)-1)&"-"&RIGHT(A2,1))

 

[aquad6]

Book1
	A	B
2	AAAB	AAA-B
3	AAAA	AAA-A
4	AAAC	AAA-C
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF(AND(OR(RIGHT(A2,1)={"A","B","C"})),LEFT(A2,LEN(A2)-1)&"-"&RIGHT(A2,1))

Amazing!!! Thank you sooo much!!!

 

[aquad6]

One more question, If I have a sku that is MO456CD, is there a formula to input a dash in between the last numeric and first alphabet, so it reads MO456-CD. Some of my skus have two Alphabetical characters at the end while others have only one. Basically I'm looking for a formula that can put a dash in between the last numeric and first alpha character no matter how many Alpha characters are at the end of the sku.

 

[aquad6]

Amazing!!! Thank you sooo much!!!

One more question, If I have a sku that is MO456CD, is there a formula to input a dash in between the last numeric and first alphabet, so it reads MO456-CD. Some of my skus have two Alphabetical characters at the end while others have only one. Basically I'm looking for a formula that can put a dash in between the last numeric and first alpha character no matter how many Alpha characters are at the end of the sku.

 

[aquad6]

Book1
	A	B
2	AAAB	AAA-B
3	AAAA	AAA-A
4	AAAC	AAA-C
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF(AND(OR(RIGHT(A2,1)={"A","B","C"})),LEFT(A2,LEN(A2)-1)&"-"&RIGHT(A2,1))

One more question, If I have a sku that is MO456CD, is there a formula to input a dash in between the last numeric and first alphabet, so it reads MO456-CD. Some of my skus have two Alphabetical characters at the end while others have only one. Basically I'm looking for a formula that can put a dash in between the last numeric and first alpha character no matter how many Alpha characters are at the end of the sku.

 

[aquad6]

Hi There!
If I have a sku that is MO456CD, is there a formula to input a dash in between the last numeric and first alphabet, so it reads MO456-CD. Some of my skus have two Alphabetical characters at the end while others have only one. Basically I'm looking for a formula that can put a dash in between the last numeric and first alpha character no matter how many Alpha characters are at the end of the sku.

 

[Fluff]

How about

=REPLACE(A2,MAX(IFERROR(FIND(SEQUENCE(,10,0),A2),0))+1,0,"-")

 

[JamesCanale]

Some of the guys here will have a more elegant and smaller solution.

MrExcelPlayground18.xlsx
	A	B
1	MO456CD	MO456-CD
2	MX123A	MX123-A
3	123AM	123-AM
4	12AB23C	12AB23-C
Sheet8

Cell Formulas
Range	Formula
B1:B4	B1	=LET(z,A1,s,SEQUENCE(1,LEN(z)),a,MAX(ISNUMBER(VALUE(MID(z,s,1)))*s),LEFT(z,a)&"-"&RIGHT(z,LEN(z)-a))

 

[kevin9999]

Your formulas would start to get pretty long & difficult to read if you used purely native Excel functions for this. There may be some of the newer 365 functions that may do it simpler, but formulas are not my forte. Personally, I would use a UDF to do this. Place the following code in a standard module & see what you think.

Function InsertDash(r As Range) As String
    Dim s As String, i As Long, j As Long
    s = Trim(r.Value)
    For i = Len(s) To 1 Step -1
        If IsNumeric(Mid(s, i, 1)) Then
            j = i
            Exit For
        End If
    Next i
    If j = 0 Or j = Len(s) Then
        InsertDash = r.Value
        Exit Function
    Else
        s = Left(s, j) & "-" & Right(s, Len(s) - j)
    End If
    InsertDash = s
End Function

In use:

Book1
	A	B
1	MO456CD	MO456-CD
2	AAA1B	AAA1-B
3	A3AAA	A3-AAA
4	AAAC123	AAAC123
5	12abc	12-abc
6	1A2B3CCC	1A2B3-CCC
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=InsertDash(A1)