Parsing variables XXX12 and XXX2 to compare XXX, must be excel function

[pawest]

Hello VBA world... well this question is for Excel Function world:
I am trying to parse a string and return just the text within a string. The text strings are the same but the ending number variations differ. For example, I have two comparable variables "XXX12" and "XXX2" and I'm trying to parse out the XXX.

Factors making this difficult:

• The beginning text could range from X to XXXXX
• The ending numbers could be any single or double digit combination on the first variable and any single digit on the second variable, so the first variable could be "XX11" and the second variable could be "XX1" or the first "XXXX3" and the second "XXXX3"... the integers at the end of the string will rarely ever follow follow the same pattern
  • Therefore, a simple =FIND() won't work because of the variability of numbers
  • I tried a FIND("XX11",OR(0,1,2,3,4,5,6,7,8,9)) but an OR nested in FIND doesn't work
• This MUST be some combination of excel formulas and NOT a hardcode derived from VBA

I've tried functions such as LEFT, RIGHT, LEN, ISNONTEXT, ISNUMBER, FIND, CHAR, LEN, OR, SUBSTITUTE, etc... and I'm not having much luck. Please let me know if anyone has encountered this before and if you could help provide a solution.

Thanks!

 

[Ron Coderre]

Can you post a sample of all pattern combinations and the results you want from them?

 

[texasalynn]

ok so here are a couple of UDFs that will extract the text or the number

Function ExtractNumber(Target As Range) As Variant
Dim i As Integer
Dim str1 As String

For i = 1 To Len(Target)
    If IsNumeric(Mid(Target, i, 1)) Then
        str1 = str1 + Mid(Target, i, 1)
    End If
Next i
ExtractNumber = str1

End Function
Function ExtractText(Target As Range) As Variant
Dim i As Integer
Dim str1 As String

For i = 1 To Len(Target)
    If Not IsNumeric(Mid(Target, i, 1)) Then
        str1 = str1 + Mid(Target, i, 1)
    End If
Next i
ExtractText = str1

End Function

Excel 2003

	A	B
XXX12
XXX2	XXX
xxxx234	xxxx

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]3[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</TBODY>

Sheet3​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]B3[/TH]
[TD="align: left"]=extractnumber(A3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]B4[/TH]
[TD="align: left"]=ExtractText(A4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]B5[/TH]
[TD="align: left"]=ExtractText(A5)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[pgc01]

Hi

A general solution for a pattern "any number of letters followed by any number of digits", try:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1)

You may write a simpler formula if you make it specific to your case.

 

[pawest]

texasalynn, great recommendations on the functions... I may end up using those. However, this spreadsheet needs to go in the hands of many other people that don't know VBA. While a function created in VBA shouldn't matter, I would prefer to do it without any VBA whatsoever. Nonetheless, those parsing functions are awesome!

Ron Coderre, here's some example data and what I'm trying to achieve:

[TABLE="width: 271"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Variable 1[/TD]
[TD]Variable 2[/TD]
[TD]Desired Match
[/TD]
[/TR]
[TR]
[TD]XXA12
[/TD]
[TD]XXA2[/TD]
[TD]XXA[/TD]
[/TR]
[TR]
[TD]XXB9[/TD]
[TD]XXB9[/TD]
[TD]XXB[/TD]
[/TR]
[TR]
[TD]XC3[/TD]
[TD]XC3[/TD]
[TD]XC[/TD]
[/TR]
[TR]
[TD]XXXD11[/TD]
[TD]XXXD1[/TD]
[TD]XXXD[/TD]
[/TR]
[TR]
[TD]XXXXE10[/TD]
[TD]XXXXE10[/TD]
[TD]XXXXE[/TD]
[/TR]
</tbody>[/TABLE]


Here's one solution I've come up with but this is certainly not user-friendly and it's way too bulky: =IF((LEFT(T13,6)=LEFT(X13,6)),LEFT(X13,6),IF((LEFT(T13,5),LEFT(X13,5)),LEFT(X13,5), … down to ... LEFT(T13,1),LEFT(X13,1)),LEFT(X13,1),)

Thanks!

 

[pawest]

Pgc01,
I like your proposed solution a lot but I'm not getting it to work. Here was a test sample that I tried: =LEFT(T18,LEN(FIND({0,1,2,3,4,5,6,7,8,9},X18))-1)... it's returning #VALUE and I'm trying to figure out why. I tried to switch min to len but that didn't work either.

 

[pawest]

I tried this for further testing and it worked: =FIND({2},X18) ... this worked because I was searching for a 2 in the text string for this particular instance.

When I tried this to add some variability: =FIND({0,1,2},X18) ... it did not work and returned the #Value error, I even tried making the whole thing an array and that didn't work either

Any other suggestions are welcomed as always!

 

[texasalynn]

using pgc01 formula works

Excel 2003
	A	C
3	XXX12	XXX
4	XXX2	XXX
5	xxxx234	xxxx
Sheet3

Cell Formulas
Range	Formula
C3	=LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&1234567890))-1)

 

[pgc01]

Pgc01,
I like your proposed solution a lot but I'm not getting it to work. Here was a test sample that I tried: =LEFT(T18,LEN(FIND({0,1,2,3,4,5,6,7,8,9},X18))-1)... it's returning #VALUE and I'm trying to figure out why. I tried to switch min to len but that didn't work either.

Hi

That was not the formula I posted. For ex, for T18


=LEFT(T18,MIN(FIND({0,1,2,3,4,5,6,7,8,9},T18&1234567890))-1)

Remark: I don't understand why you have to look at variables 1 and 2, since you always want the alpha part, that is the same in both.

 

[pawest]

texasalynn and pgc, I now understand the function a lot better and I got it working! Thanks a lot!

One more question: could I add unique characters to this, as well? For example, I would like to do something like this: =LEFT(X13,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"_","@"},T13&1234567890_@))-1) ... It seems like that's not working because the MIN function may not understand the "_","@" ... any feedback would be great! Thanks!