Formula Help - Numeric Alpha Combination

[miggy999]

Hi All,

I really would appreciate some help with a formula.

I am trying to extract 4 characters which will be sitting together in a string, however, the particular 4 characters I am looking for will be sitting as a NUMBER/LETTER/LETTER/NUMBER.

For example, from the string below I am trying to get 4LA6 as the answer.
[TABLE="width: 276"]
<tbody>[TR]
[TD]LF-074LA6150-GB43602[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

If it is relevant there can be dashes anywhere in the string (but never between any of the 4 characters), and the numbers can be zero.

Can anyone help?

 

[FormR]

Hi, you could give this UDF a try:

1. With your spreadsheet open, press ALT+F11 to open the VBE.
2. Click "Insert" > "Module" on the menu bar.
3. Copy and paste the code into the blank window on the top right hand side.
4. Press ALT+Q to close the VBE
5. Save your workbook as a macro enabled workbook (xlsm)
6. Use just like another worksheet function as demonstrated below.

Function NumLetLetNum(S As String) As String
Dim I As Long
For I = 1 To Len(S)
    If Mid(S, I, 4) Like "[0-9][A-z][A-z][0-9]" Then NumLetLetNum = NumLetLetNum & ", " & Mid(S, I, 4)
Next I
NumLetLetNum = Mid(NumLetLetNum, 3)
End Function

Excel 2012
	A	B
1	LF-074LA6150-GB43605	4LA6
2	LF-0Kz04A6150-GB43605	0Kz0
3	LF-074LA6150-5GB43605	4LA6, 5GB4
Sheet1

Cell Formulas
Range	Formula
B1	=NumLetLetNum(A1)

 

[XOR LX]

Hi.

=MID(A1,MATCH(4,MMULT(ABS({0,1,1,0}-ISNUMBER(0+MID(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-3)),4),{1,2,3,4},1))),{1;1;1;1}),0),4)

Regards

 

[FormR]

Hi XOR LX,

Not sure if this is possible with the OP's data, but something for them to consider:

Excel 2012
	A	B
1	ABC 1##11A11A	1##1
2	FD1S31--511AA1	1--5
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,MATCH(4,MMULT(ABS({0,1,1,0}-ISNUMBER(0+MID(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-3)),4),{1,2,3,4},1))),{1;1;1;1}),0),4)

 

[XOR LX]

@FormR

Good point. It will be a necessarily more complex solution if such strings are a possibility, and of course your code would win hands down!

I'll post a revision if the OP asks for one, though of course unless they specifically wish to avoid VBA then they should of course go with your code.

Cheers

 

[XOR LX]

I think this variation should return only strings which satisfy:

[Numeric][Upper-Case Letter][Upper-Case Letter][Numeric]

=MID(A1,MATCH(51,MMULT(0+(TEXT(ABS(77.5-CODE(TEXT(MID(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-3)),4),{1,2,3,4},1),"[>=0]""@"""))),"[=13.5]5;[<13]1;99")),{1;2;4;8}),0),4)

Regards

 

[miggy999]

Wow! That was so fast. Thank you very much.

FormR : I have never used the VBE before and this solution was easy and worked. Thank you so much for your time.

XOR LX : Thank you for your effort too. I cant pretend to get my head around your formula. It had a problem where "-" was present. For example "5-C254TB5-03374-V1", the output from the formula is "5-C2" as it presumbaly counts the "-" as a letter and therefore matches the number/letter/letter/number combination. I was looking for a "-" to be counted as neither and the output "4TB5". But anyway, thank you so much.


Regards,
Neil

 

[XOR LX]

XOR LX : Thank you for your effort too. I cant pretend to get my head around your formula. It had a problem where "-" was present. For example "5-C254TB5-03374-V1", the output from the formula is "5-C2" as it presumbaly counts the "-" as a letter and therefore matches the number/letter/letter/number combination. I was looking for a "-" to be counted as neither and the output "4TB5". But anyway, thank you so much.

Did you see my revised solution?

Regards

 

[István Hirsch]

Hi XOR LX,

Not sure if this is possible with the OP's data, but something for them to consider:

Excel 2012

	A	B
1##1
1--5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]ABC 1##11A11A[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]FD1S31--511AA1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=MID(A1,MATCH(4,MMULT(ABS({0,1,1,0}-ISNUMBER(0+MID(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-3)),4),{1,2,3,4},1))),{1;1;1;1}),0),4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

If not only letters and digits are allowed, check this:

[TABLE="width: 210"]
<tbody>[TR]
[TD="width: 210"]LF-074\_6150-4GB43602[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

If not only letters and digits are allowed, check this:

Hi Istvan - I assume you meant to quote post#2, if so a slight modification:

Function NumLetLetNum(S As String) As String
Dim I As Long
For I = 1 To Len(S)
    If Mid(S, I, 4) Like "[0-9][A-Za-z][A-Za-z][0-9]" Then NumLetLetNum = NumLetLetNum & ", " & Mid(S, I, 4)
Next I
NumLetLetNum = Mid(NumLetLetNum, 3)
End Function

Or if only upper case letters are valid:

Function NumLetLetNum(S As String) As String
Dim I As Long
For I = 1 To Len(S)
    If Mid(S, I, 4) Like "[0-9][A-Z][A-Z][0-9]" Then NumLetLetNum = NumLetLetNum & ", " & Mid(S, I, 4)
Next I
NumLetLetNum = Mid(NumLetLetNum, 3)
End Function