Find a word that contain 6 digit in a cell using formula

[oniototo]

Example: A1 Cell contain "Psd4 j%65 K56L456%4 Jin k444"
Result after Formula : K56L456%4

is there any formula to find the word that contain 6 digit in a cell?

 

[Zot]

I don't think it is possible with or perhaps it is simpler to use UDF

Put this custom function in regular module

Function GetNum(rng As Range) As String

Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = ".?\d.?\d.?\d.?\d.?\d.?\d"
    .Global = False
End With
If RegEx.Test(rng.Value) Then GetNum = RegEx.Execute(rng)(0)

End Function

To use

Book1
	A	B	C	D	E	F	G
1	Psd4 j%65 K56L456%4 Jin k444					K56L456%4
2
Sheet1

Cell Formulas
Range	Formula
F1	F1	=GetNum(A1)

 

[Peter_SSs]

Welcome to the MrExcel board!

I assume that you mean "exactly 6 digits" in the 'word' rather than "6 or more"

If so, you could try the formula in column B.
Alternatively, below is a user-defined function that you could also consider. Its use is shown in columns C & D, where it is used with its optional second argument if you wanted to look for words with a different number of digits

For both of my suggestions, if it is possible that 2 or more 'words' contain the correct number of digits, the first one is returned.

Function nDigitWord(s As String, Optional NumDigits As Long = 6) As String
  Dim itm As Variant
  Dim i As Long, c As Long
 
  For Each itm In Split(s)
    c = 0
    For i = 1 To Len(itm)
      If IsNumeric(Mid(itm, i, 1)) Then c = c + 1
    Next i
    If c = NumDigits Then
      nDigitWord = itm
      Exit Function
    End If
  Next itm
End Function

oniototo.xlsm
	A	B	C	D
1	Psd4 j%65 K56L456%4 Jin k444	K56L456%4	K56L456%4	j%65
2	K56L456%4 Jin k445	K56L456%4	K56L456%4
3	K56L456%4 Jin k446 K56L999%4 Jin k446	K56L456%4	K56L456%4
4	Psd4 j%65 K56L456%4	K56L456%4	K56L456%4	j%65
5	Psd4 j%6 K56L456%
6	Psd4 j%65 K56986547L456%			j%65
7	j%65 K56986547L456% G678987R	G678987R	G678987R	j%65
6 digits

Cell Formulas
Range	Formula
B1:B7	B1	=LET(t,MID(A1,SEQUENCE(LEN(A1)),1),ck,CONCAT(IF(t=" ",t,IF(ISNUMBER(t+0),1,""))),tt,LEFT(ck,FIND(" "&111111&" "," "&ck&" ")),IFERROR(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),(LEN(tt)-LEN(SUBSTITUTE(tt," ","")))*100+1,100)),""))
C1:C7	C1	=nDigitWord(A1)
D1:D7	D1	=nDigitWord(A1,2)

@Zot
Your udf fails if data like row 7 in my sample is possible.

 

[Peter_SSs]

@Zot
Your udf fails if data like row 7 in my sample is possible.

For a regular expressions udf, this might be a possibility

Function GetWord(s As String) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "( |^)(([^ \d]*\d){6}[^ \d]*)(?= |$)"
  If RX.Test(s) Then GetWord = LTrim(RX.Execute(s)(0))
End Function

oniototo.xlsm
	A	B
1	Psd4 j%65 K56L456%4 Jin k444	K56L456%4
2	K56L456%4 Jin k445	K56L456%4
3	K56L456%4 Jin k446 K56L999%4 Jin k446	K56L456%4
4	Psd4 j%65 K56L456%4	K56L456%4
5	Psd4 j%6 K56L456%
6	Psd4 j%65 K56986547L456%
7	j%65 K56986547L456% G678987R	G678987R
6 digits (2)

Cell Formulas
Range	Formula
B1:B7	B1	=GetWord(A1)