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



## rsd007 (Jan 2, 2023)

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


----------



## etaf (Jan 2, 2023)

add an IFERROR
 =IFERROR(MID(B1,SEARCH("@",B1)+1,8),"")

that will replace the value error with a blank

not sure it needs to be an array formula - {}
as you are only searching a cell for a character


----------



## rsd007 (Jan 2, 2023)

Thankyou for the reply,
If the length of number changes from 8 digit to 5 it pulls alphabet or whatever is after numbers


----------



## etaf (Jan 2, 2023)

yes it will , as it would have in the original formula
as the string is set to 8 +1 so 9

Will there only ever be 5 or 8 digits

but also you have spaces after the @ so gets a bit complicated

*can you provide a few more possible examples*

I have a complicated way to extract just the information between the @ and a 2nd Space
but i'm not happy with the solution
its late now in UK - so I may have a look into other possible solutions......

*hopefully other members may help here*

but here is the really messy formula, and may not work for all possible examples you may have
for example if there is


```
abc @       1234 5
```

it will not work - as to many spaces after the @ 

as i say - thinking about it - further - this is not good - so maybe IGNORE 

reported , in case needs a new thread as new question


=IFERROR(MID(A1,SEARCH("@",A1,1)+1,SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1)),"")
may not work for all possible combinations


Book10ABC112aaaasssss aaaaa @ 34567 xxxx3456721 aaaaa @ 34 xxxx34312assss aaaaa @ 34567456789 xxxx34567456789412aaaasssss aaaaa @345xxxxxx67bbbbbbbgggg xxxx345xxxxxx67bbbbbbbgggg 512aaaasssss aaaaa  34567 xxxx 612aaaasssss aaaaa @abc xxxxabc Sheet2Cell FormulasRangeFormulaC1:C6C1=IFERROR(MID(A1,SEARCH("@",A1,1)+1,SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1)),"")


----------



## Rick Rothstein (Jan 2, 2023)

etaf said:


> add an IFERROR
> =IFERROR(MID(B1,SEARCH("@",B1)+1,8),"")


You could also do it this way...

=MID(B1,FIND("@",B1&"@")+1,8)

As for the follow up question about 5 or 8 characters... your request for more examples is what is needed.


----------



## rsd007 (Jan 3, 2023)

Thankyou for the Help,

Some of the entry are like. 
Have text after numbers with no gaps


----------



## JEC (Jan 3, 2023)

Put this behind a code module.
Then you can use it as a normal function as:  


```
=jec(A1)
```



```
Function jec(xStr As String) As Variant
 With CreateObject("vbscript.regexp")
   .Pattern = "(.*@\s?)(.\d+)(.*)"
   If .test(xStr) Then jec = .Replace(xStr, "$2") Else jec = "no match"
 End With
End Function
```


----------



## etaf (Jan 3, 2023)

my solution wont work - i will need to revisit 

BUT you dont have anything where after the @ , you have more than 1 space - never going to happen ?

you have shown all possible variations now .... from post #1 and post #6


----------



## etaf (Jan 3, 2023)

as mentioned before - this is a messy formula 

=IFERROR(MID(A1,SEARCH("@",A1,1)+1,IFERROR(SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1),LEN(A1)-SEARCH("@",A1,1))),"")

so we are looking to see , there is an @ in the formula - if not , then the IFERROR will return a space 

then we are extracting the value after the @ 
using a MID () 
Mid is made up of - Cell, startnumber and number of characters 
SEARCH("@",A1,1)+1
we find the @ and then add 1 - so we get the start number
Now we need to know how much to extract 
we are looking for a space after the @ - but not if immediately after the @ - like "@ "
we search for a space after 2 characters of the @ - so a 2nd space SEARCH(" ",A1,SEARCH("@",A1,1)+2)
Now we have that position of the 2nd space 
then back to find the position of the @ 
we can subtract to get the length form the @ to the space 
BUT if there is no 2nd space after the @ - such as "@ 123"
then we get an error - because no 2nd space 
in which case we just need to find the length of the cell text and subtract the position of the @ to get the characters to extract 

I'm sure there must be a simple way to do this 

you will get a space in the result if a space after the @ , which you show in your examples

=IFERROR(MID(A1,SEARCH("@",A1,1)+1,IFERROR(SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1),LEN(A1)-SEARCH("@",A1,1))),"")

to remove any spaces in the result leading
use
TRIM()





Book10ABC112345678    @ 12345 678912345212345@6789 xxxx6789312assss aaaaa @ 34567456789 xxxx34567456789412aaaasssss aaaaa @345xxxxxx67bbbbbbbgggg xxxx345xxxxxx67bbbbbbbgggg 512aaaasssss aaaaa  34567 xxxx 612aaaasssss aaaaa @abc xxxxabc 7123 cxxxx @1231238@1231239123 10aaa2345 11@aaaaSheet2Cell FormulasRangeFormulaC1:C11C1=IFERROR(MID(A1,SEARCH("@",A1,1)+1,IFERROR(SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1),LEN(A1)-SEARCH("@",A1,1))),"")


----------



## Rick Rothstein (Jan 3, 2023)

rsd007 said:


> Thankyou for the Help,
> 
> Some of the entry are like.
> Have text after numbers with no gaps
> View attachment 81942


You really should show the expected result for these, so we don't have to guess. For example, in your first message you show an expected result with an "X" inside the number, so for the second example above, I assume the result would be at least A2346, but what about the lower case letters... are they to be included or excluded? And, if excluded, is that because they are not upper case? Now, what about the last example above... is the GRR to be included or not? If not included, why?


----------



## rsd007 (Jan 2, 2023)

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 (Jan 3, 2023)

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 (Jan 3, 2023)

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


----------



## JEC (Jan 3, 2023)

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


Book1BCD1lattice 4X8 vinyl whiteno match2privacy lattice white type @A2356yjr23563Lattice vinyl grey @66967GRR669674Ground Contact @456-3449 blabla c 668545634495above @96X122139612213Sheet1Cell FormulasRangeFormulaD1:D5D1=jec(B1)


----------



## Rick Rothstein (Jan 3, 2023)

rsd007 said:


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


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 (Jan 3, 2023)

Rick Rothstein said:


> 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 (Jan 3, 2023)

JEC said:


> You totally ignored my suggestion?
> 
> 
> ```
> ...


Hello JEC,

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


----------



## rsd007 (Saturday at 7:23 PM)

Rick Rothstein said:


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


----------

