Extract Data Between Symbols Depending on Situation

[Stevan Premovic]

I've been trying to write a formula that will extract the number after the @ symbol in a number of situations, but everything I've come up with thus far won't work for every case.

Lets say in cells A1:A5 I have:

[BALL VALVE,-RF,CL600 @ 4, 10XAH007]
[BALL VALVE,-RF,CL600,SPINDLE ELONGATION @ 4; 10XAH011]
[GATE VALVE,-RF,CL600 @ .5]
[GLOBE VALVE,-RF,CL600 @ .75]
[AXIAL CHECK VALVE@ 6,-RF, CL300, 21XAE501

What I'm trying to extract is 4, 4, .5, .75 and 6, respectively.

What I initially started with was to extract a certain number of characters to the right of the @ symbol, but that left me with additional commas, and in some cases some symbols.

Any help would be greatly appreciated

 

[Fluff]

One option, using a UDF

Function GetNum(Rng As Range, Optional delim As String) As Double
   GetNum = Split(Split(Rng, "@")(1), delim)(0)
End Function

and use like =GetNum(F6,",") where the part in red is the delimiter after the number

 

[jtakw]

Hi,

Here's a formula solution also:

Book1
	A	B	C
1	[BALL VALVE,-RF,CL600 @ 4, 10XAH007]	4	4
2	[BALL VALVE,-RF,CL600,SPINDLE ELONGATION @ 4; 10XAH011]	4	4
3	[GATE VALVE,-RF,CL600 @ .5]	0.5	.5
4	[GLOBE VALVE,-RF,CL600 @ .75]	0.75	.75
5	[AXIAL CHECK VALVE@ 6,-RF, CL300, 21XAE501	6	6
Sheet60

Cell Formulas
Range	Formula
B1	=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,FIND("@",A1)+1,255)),";",","),"]",","),",",REPT(" ",30)),15)+0
C1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,FIND("@",A1)+1,255)),";",","),"]",","),",",REPT(" ",30)),15))

Use C1 formula if you just want the number string extracted, results will be TEXT, exactly as show in original data string.
Use B1 formula if you want the results converted to Real Numbers for further math.

Either way, formulas copied down.

 

[Stevan Premovic]

This works perfectly. Thank you!

 

[jtakw]

You're welcome.

 

[Rick Rothstein]

One option, using a UDF

Function GetNum(Rng As Range, Optional delim As String) As Double
   GetNum = Split(Split(Rng, "@")(1), delim)(0)
End Function

and use like =GetNum(F6,",") where the part in red is the delimiter after the number

Another possibility without needing to know the delimiter...

Function GetNum(S As String) As Double
  GetNum = Val(Split(S, "@")(1))
End Function