Find number after specific letter in cell

[enigma04]

Hi,

Is there a formula or vb code i can use to find a numbers after a specific letter in a cell no matter where that letter is in the cell?

Example: G0Z-1.4743Y1.2479X2..... I would like to find the 1.2479 after the "y" and use it in a formula. My goal is to find that number, divide it by .0076358155 and replace the existing number with the answer. Is this possible?

Thanks,

Kevin

 

[enigma04]

Thanks to everyone for all your help!

=IF(ISNUMBER(SEARCH("y",A1)),REPLACE(A1,FIND("Y",A1)+1,6,LEFT(TEXT(MID(A1,FIND("Y",A1)+1,6)/0.0076358155,"0.00000"),6)),A1)

^^worked! But now i have another issue. I mentioned before that the number should always be 6 characters. Well, that has changed. now i have some cells such as: Z-1.3535Y1.443F12.00, in addition to the existing cells.

 

[barry houdini]

So in that case if you take the 1.443 and do the calculation how many characters do you want to use in the result, should it be the same as that number (5) or still 6 as before? How many characters can the number have, can it be more than 6?

 

[enigma04]

Ok, I spoke with the person I'm needing this for. He says the numbers will vary per program. (these are program numbers for cnc machines) So i could have Y11.234, Y1.234, Y0.05466 etc. But I now know that i only need 3 decimal places because the machine can't read past that ie. 1.003. But more is fine.

Thanks

 

[barry houdini]

This should work for up to 9 character numbers (8 digits and a decimal point)

=IF(COUNTIF(A1,"*Y*"),SUBSTITUTE(A1,"Y"&LOOKUP(9^99,MID(A1,FIND("Y",A1)+1,{1,2,3,4,5,6,7,8,9})+0),"Y"&FIXED(LOOKUP(9^99,MID(A1,FIND("Y",A1)+1,{1,2,3,4,5,6,7,8,9})+0)/0.0076358155,3,1),1),A1)

 

[enigma04]

Barry, you rock!! ray: Thanks for all your help!