Search for a numerical value in the string

[simasemail]

Hello, I need i bit of your help with excel.

I am trying to create simple excel spreadsheet that can calculate price for material. However i don't know, how to create a function which can identify numerical value the string.

For example I have a string from CAD software like X: 14.31 Y: 78.86 Z: 295.42
Numbers indicates dimensions in mm. It can be anything from 0 to infinity.

I want to create a function which can take x, y and z values and plot them separately on C4,C5,C6.

Does the excel has a function to do it?
Value in the string.xlsx

Thanks

Simon

[Special-K]

in C4
=MID(C1,4,FIND("Y:",C1)-4)
in C5
=MID(C1,FIND("Y:",C1)+3,FIND("Z:",C1)-(FIND("Y:",C1)+3))
in C6
=RIGHT(C1,LEN(C1)-FIND("Z:",C1)-2)

[Russell Dawson]

Hi,

With respect, Im not sure Special K has it right as the result is not numeric.

I've been toying with Text to columns and made a macro to do as requested. See attached.

[Indi_Ra]

Hi,

With respect, Im not sure Special K has it right as the result is not numeric.

@Russel
Neither your code does not return values, but text.

Special-K Formulas are OK but the numbers must be put "+0" at the end of formulas (without quotes).
=MID(C1,4,FIND("Y:",C1)-4)+0
Or
=1*MID(C1,4,FIND("Y:",C1)-4)

[Russell Dawson]

So I was right then.

My solution works fine albeit not by formula. The calc works fine.

[newdoverman]

Is your real data just 1 row like you show or is it several rows of XYZ values? If that is the case, what does it really look like?

[AlKey]

You can accomplish this one formula

In C4 and copy down

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C$1,"X: ",""),"Y: ",""),"Z: ","")," ",REPT(" ",255)),(ROWS($A$1:A1)-1)*255+1,255))