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

 

[etaf]

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]

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

 

[etaf]

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

Book10
	A	B	C
1	12aaaasssss aaaaa @ 34567 xxxx		34567
2	1 aaaaa @ 34 xxxx		34
3	12assss aaaaa @ 34567456789 xxxx		34567456789
4	12aaaasssss aaaaa @345xxxxxx67bbbbbbbgggg xxxx		345xxxxxx67bbbbbbbgggg
5	12aaaasssss aaaaa 34567 xxxx
6	12aaaasssss aaaaa @abc xxxx		abc
Sheet2

Cell Formulas
Range	Formula
C1:C6	C1	=IFERROR(MID(A1,SEARCH("@",A1,1)+1,SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1)),"")

 

[Rick Rothstein]

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]

Thankyou for the Help,

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

 

[JEC]

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]

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]

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

Book10
	A	B	C
1	12345678 @ 12345 6789		12345
2	12345@6789 xxxx		6789
3	12assss aaaaa @ 34567456789 xxxx		34567456789
4	12aaaasssss aaaaa @345xxxxxx67bbbbbbbgggg xxxx		345xxxxxx67bbbbbbbgggg
5	12aaaasssss aaaaa 34567 xxxx
6	12aaaasssss aaaaa @abc xxxx		abc
7	123 cxxxx @123		123
8	@123		123
9	123
10	aaa2345
11	@aa		aa
Sheet2

Cell Formulas
Range	Formula
C1:C11	C1	=IFERROR(MID(A1,SEARCH("@",A1,1)+1,IFERROR(SEARCH(" ",A1,SEARCH("@",A1,1)+2)-SEARCH("@",A1,1),LEN(A1)-SEARCH("@",A1,1))),"")

 

[Rick Rothstein]

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?