Excel matching letters

Question

I'm trying to have a way in Excel that allows me to say that

ABC-DEF is the same as DEF-ABC

Anyone know a trick on how to do that?

Do you want the matching to be case-sensitive (i.e. ABC is the same as AbC)? Is the format XXX-YYY fixed (i.e. two strings of length 3 separated by an hyphen)? What have you tried? — user2314737, Aug 16 '17 at 08:22

Vityata · Answer 1 · 2017-08-16T08:21:49.447

See how to ask questions: https://stackoverflow.com/help/how-to-ask

Sometimes, but only sometimes, (featuring manchmal aber nur manchmal) you may get lucky posting a question like this getting answered:

Option Explicit

Public Sub TestMe()

    Debug.Print CompareMe("ABC-DEF", "DEF-ABC")
    Debug.Print CompareMe("ABC-DEF", "DEF-AAC")

End Sub

Public Function CompareMe(strCompareA As String, strCompareB As String, Optional strDelim = "-")

    Dim arrA    As Variant
    Dim arrB    As Variant

    arrA = Split(strCompareA, strDelim)
    arrB = Split(strCompareB, strDelim)

    CompareMe = (arrA(0) = arrB(1) And arrA(1) = arrB(0))

End Function

Check the TestMe sub, it prints the result in the console.

Furthermore, this is an interview algorithm question, asked by Google. This is a general working answer - reverse the whole string, then split by word and reverse each word. This is how to do it with VBA:

Option Explicit

Public Sub TestMe()

    Debug.Print strReverseOrder("ABC-DEF-GHI")
    Debug.Print strReverseOrder("ABC-DEF") = "DEF-ABC"

End Sub

Public Function strReverseOrder(strToReverse As String, Optional strDelim = "-")

    Dim strWorking          As String
    Dim arrWorking()        As String
    Dim lngCounter          As Long
    Dim strAnswer           As String

    strWorking = StrReverse(strToReverse)
    arrWorking = Split(strWorking, strDelim)

    For lngCounter = LBound(arrWorking) To UBound(arrWorking)
        strAnswer = IIf(lngCounter = LBound(arrWorking), vbNullString, strAnswer & strDelim) & StrReverse(arrWorking(lngCounter))
    Next lngCounter

    strReverseOrder = strAnswer

End Function

score 1 · Answer 2 · answered Feb 18 '18 at 17:10

1

Here’s the formula

=IF(AND(RIGHT(A8,3)=LEFT(A9,3),LEFT(A8,3)=RIGHT(A9,3)),TRUE,FALSE)

answered Feb 18 '18 at 17:10

Dude_Scott

641
5
9

score 0 · Accepted Answer · answered Feb 18 '18 at 16:28

0

The following worked for me:

=IF(D2>H2,CONCATENATE(D2,"-",H2),CONCATENATE(H2,"-",D2))

answered Feb 18 '18 at 16:28

Neicooo

197
1
9

score -1 · Answer 4 · answered Aug 16 '17 at 08:25

-1

Assuming this is A1 put this in B1: =IF(A1="ABC-DEF","DEF-ABC",A1). Drag down to fill in.

answered Aug 16 '17 at 08:25

krib

569
4
14

How does that relate to the issue? – teylyn Aug 16 '17 at 08:56
@teylyn the question, as I read it, this is what the issue is; he/she wants to get the "abc..." as "def...." assuming this is in a column. The question is open for interpretation and this might be one solution. – krib Aug 16 '17 at 09:03
The question is open to interpretation. So far so good. The way I read it is A1 has "ABC-DEF" and B1 has "DEF-ABC". Create a formula in C1 that compares the two cells and shows "TRUE". You don't compare two values. You only take one and make an assumption. – teylyn Aug 16 '17 at 09:07
the assumption is this; if "ABD-DEF" is the same as "DEF-ABC" then there wouldn't be any need for column A. What he/she wants to do with this result, that I cant predict. This only moves all values from `A` over to `B` and replaces "ABC-DEF" – krib Aug 16 '17 at 09:18
You're not getting the point. Two cells. Compare them and determine if their content is the same. One has "ABC-DEF", the other has "DEF-ABC". How would you do it? Your approach does not take into consideration two cells, but just one. You are not comparing anything. – teylyn Aug 16 '17 at 09:20
thats not the question either. Question is only to get them to be the same. But I see your point, and i agree with you on that. Vityata's answer looks for me to be the way in VBA. – krib Aug 16 '17 at 09:22

Excel matching letters

4 Answers4