Since you asked about RegExp, here is a UDF that I think does what you want. Note that by default it searches for exactly 3 digits, but the optional 2nd argument allows you to search for a different number of digits if you want, as shown in C6 below.
Code:
Function FirstN(s As String, Optional Num As Long = 3) As Variant
Static RX As Object
If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
RX.Pattern = "(^|\D)(\d{" & Num & "})(\D|$)"
FirstN = ""
If RX.Test(s) Then FirstN = Val(RX.Execute(s)(0).SubMatches(1))
End Function
Sheet2
| A | B | C |
text 777 moretext 555 | | | |
text 34 das 765das 654 | | | |
365 crde | | | |
ewtet 55 | | | |
| | | |
q 12 wer 3456 te 540 er | | | |
abc 900 | | | |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:172px;"><col style="width:53px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]777[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]765[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]365[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]540[/TD]
[TD="align: right"]3456[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]900[/TD]
</tbody>
Spreadsheet Formulas |
Cell | Formula | B1 | =FirstN(A1) | C6 | =FirstN(A6,4) |
<tbody>
</tbody> |
<tbody>
</tbody>