Formula Help - Numeric Alpha Combination

miggy999

New Member
Joined
Nov 17, 2015
Messages
2
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.
LF-074LA6150-GB43602

<tbody>
</tbody><colgroup><col></colgroup>

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?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.

Code:
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
AB
1LF-074LA6150-GB436054LA6
2LF-0Kz04A6150-GB436050Kz0
3LF-074LA6150-5GB436054LA6, 5GB4
Sheet1
Cell Formulas
RangeFormula
B1=NumLetLetNum(A1)
 
Upvote 0
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
 
Upvote 0
Hi XOR LX,

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


Excel 2012
AB
1ABC 1##11A11A1##1
2FD1S31--511AA11--5
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi XOR LX,

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

Excel 2012
AB
1ABC 1##11A11A1##1
2FD1S31--511AA11--5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

LF-074\_6150-4GB43602

<tbody>
</tbody>
 
Upvote 0
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:

Code:
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:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top