# Formula Help - Numeric Alpha Combination

#### miggy999

##### New Member
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### FormR

##### MrExcel MVP
Hi, you could give this UDF a try:

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``````

#### XOR LX

##### Well-known Member
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

##### MrExcel MVP
Hi XOR LX,

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

#### XOR LX

##### Well-known Member

@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

##### Well-known Member
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

##### New Member

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

##### Well-known Member
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

##### Well-known Member
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

</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)

</tbody>

<tbody>
</tbody>

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

 LF-074\_6150-4GB43602

<tbody>
</tbody>

#### FormR

##### MrExcel MVP
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:

Replies
10
Views
308
Replies
3
Views
59
Replies
5
Views
201
Replies
4
Views
126
Replies
1
Views
133

1,136,845
Messages
5,678,092
Members
419,742
Latest member
Dropzyl88

### 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.

### Which adblocker are you using?

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

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