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

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
Joined
Aug 18, 2011
Messages
6,536
Office Version
  1. 365
Platform
  1. Windows
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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">LF-074LA6150-GB43605</td><td style=";">4LA6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">LF-0Kz04A6150-GB43605</td><td style=";">0Kz0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">LF-074LA6150-5GB43605</td><td style=";">4LA6, 5GB4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=NumLetLetNum(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Aug 18, 2011
Messages
6,536
Office Version
  1. 365
Platform
  1. Windows
Hi XOR LX,

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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;background-color: #FFFFFF;;">ABC 1##11A11A</td><td style="color: #0000CD;;">1##1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;background-color: #FFFFFF;;">FD1S31--511AA1</td><td style="color: #0000CD;;">1--5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,MATCH(<font color="Red">4,MMULT(<font color="Green">ABS(<font color="Purple">{0,1,1,0}-ISNUMBER(<font color="Teal">0+MID(<font color="#FF00FF">MID(<font color="Navy">A1,ROW(<font color="Blue">INDEX(<font color="Red">A:A,1</font>):INDEX(<font color="Red">A:A,LEN(<font color="Green">A1</font>)-3</font>)</font>),4</font>),{1,2,3,4},1</font>)</font>)</font>),{1;1;1;1}</font>),0</font>),4</font>)</td></tr></tbody></table></td></tr></table><br />
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

@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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Nov 17, 2015
Messages
2

ADVERTISEMENT

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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
May 16, 2013
Messages
1,634
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>
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,536
Office Version
  1. 365
Platform
  1. Windows
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:

Forum statistics

Threads
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.
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
Top