Extract a string of variable length and position from a cell getting #VALUE!

[rsd007]

Hello,
I am pulling values after special character in a string. In some case I will get the value but if special character is not in the string will get Error #VALUE!

I am using Formula =MID(B1,SEARCH("@",B1)+1,8) in Cell D1

 

[rsd007]

Thankyou etaf and Rick,
Thankyou for the replies, I am looking / end result as to extract numbers only (without any lower or upper case letters)

Cell C1 should be Blank ( does not have @ in)
Cell C2 Should be 2356 ( does have @ and should ignore letter A and yjr )
Cell C3 Should be 66967 ( does have @ and should ignore letter GRR )
Regards

 

[etaf]

i dont think i can help out much further with that question

 

[JEC]

You totally ignored my suggestion?

Function jec(xStr As String) As Variant
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "(.*@\s?)(.*?)((\s.*)|$)"
   If .test(xStr) Then
     jec = .Replace(xStr, "$2")
     .Pattern = "[^0-9]"
     jec = .Replace(jec, "")
   Else
     jec = "no match"
   End If
 End With
End Function

Book1
	B	C	D
1	lattice 4X8 vinyl white		no match
2	privacy lattice white type @A2356yjr		2356
3	Lattice vinyl grey @66967GRR		66967
4	Ground Contact @456-3449 blabla c 6685		4563449
5	above @96X12213		9612213
Sheet1

Cell Formulas
Range	Formula
D1:D5	D1	=jec(B1)

 

[Rick Rothstein]

Thankyou etaf and Rick,
Thankyou for the replies, I am looking / end result as to extract numbers only (without any lower or upper case letters)

Cell C1 should be Blank ( does not have @ in)
Cell C2 Should be 2356 ( does have @ and should ignore letter A and yjr )
Cell C3 Should be 66967 ( does have @ and should ignore letter GRR )
Regards

In your first message, cell B5 had this in it.. @96X12213 and result you wanted for it was 96X12213. What is the rule concerning letters... why was this "X" retained and the "A" in the second example above removed? If the letter is inside the digits keep it, if it is on the end, remove it?

 

[rsd007]

In your first message, cell B5 had this in it.. @96X12213 and result you wanted for it was 96X12213. What is the rule concerning letters... why was this "X" retained and the "A" in the second example above removed? If the letter is inside the digits keep it, if it is on the end, remove it?

Yes , Letter inside numbers will always be X and yes will be part of the answer so the answer 96X12213 is good. anytime letters outside to be removed like A2356Yjr should be 2356 only. 66967GRR should be 66967.

 

[rsd007]

You totally ignored my suggestion?

Function jec(xStr As String) As Variant
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "(.*@\s?)(.*?)((\s.*)|$)"
   If .test(xStr) Then
     jec = .Replace(xStr, "$2")
     .Pattern = "[^0-9]"
     jec = .Replace(jec, "")
   Else
     jec = "no match"
   End If
 End With
End Function

Book1
	B	C	D
1	lattice 4X8 vinyl white		no match
2	privacy lattice white type @A2356yjr		2356
3	Lattice vinyl grey @66967GRR		66967
4	Ground Contact @456-3449 blabla c 6685		4563449
5	above @96X12213		9612213
Sheet1

Cell Formulas
Range	Formula
D1:D5	D1	=jec(B1)

Hello JEC,

Have not ignored your code.
Problem is I have to do this without Vb script.
Thanks

 

[rsd007]

In your first message, cell B5 had this in it.. @96X12213 and result you wanted for it was 96X12213. What is the rule concerning letters... why was this "X" retained and the "A" in the second example above removed? If the letter is inside the digits keep it, if it is on the end, remove it?

Hello Rick,

Any help,

Letter inside numbers will always be X and yes will be part of the answer so the answer 96X12213 is good. anytime letters outside to be removed like A2356Yjr should be 2356 only. 66967GRR should be 66967.