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

 

[barry houdini]

Hello Kevin, welcome to MrExcel

Will the number always be 6 characters, including the decimal point? What about the new number should that take up the same number of characters?

 

[AlphaFrog]

Is the "number" always followed by an X ?

 

[machopicho]

=mid(a1,find("y",a1)+1,6)/.0076358155

 

[enigma04]

getting #value!...

 

[enigma04]

The number should always be 6 characters including the decimal point but will not always be followed by an "x"...


Examples:
Z-1.3545Y1.6564
Z-1.3535Y1.2734F12.00
G0Z-1.4743Y1.2479X2.

 

[AlphaFrog]

The FIND function is case sensitive. Try this...
=MID(A1,FIND("Y",A1)+1,6)/0.0076358155

Or use the SEARCH function (not case sensitive)...
=MID(A1,SEARCH("y",A1)+1,6)/0.0076358155

 

[barry houdini]

Try this formula to extract the value, do the calculation and then replace it.......

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

 

[cb12]

I'm sure this can be tidied up but in vba it can be done something like this

Sub Number_Replacer()

Dim StrLen, j As Integer
Dim i, FirstPart, SecondPart, ThirdPart As String
Dim Divi, divfig As Double
    i = ActiveCell.Value
    j = InStr(1, i, "Y", vbTextCompare)
    StrLen = Len(i)
    divfig = 0.0076358155
        
        FirstPart = Left(i, j)
    
        SecondPart = Left(Right(i, StrLen - j), 6)
    
        ThirdPart = Right(i, StrLen - j - 6)
   
        Divi = CDbl(SecondPart)
        Divi = Divi / divfig
    
    ActiveCell.Offset(0, 1).Value = FirstPart & Divi & ThirdPart

End Sub

 

[scottylad2]

Excel Workbook
	E	F	G
15	Z-1.3545Y1.6564	1.6564	216.9251
16	Z-1.3535Y1.2734F12.00	1.2734	166.7667
17	G0Z-1.4743Y1.2479X2.	1.2479	163.4272
Group

Excel 2010

Cell Formulas
Range	Formula
F15	=RIGHT(MID(E15,SEARCH("y",E15),7),6)
F16	=RIGHT(MID(E16,SEARCH("y",E16),7),6)
F17	=RIGHT(MID(E17,SEARCH("y",E17),7),6)
G15	=RIGHT(MID(E15,SEARCH("y",E15),7),6)/0.0076358155
G16	=RIGHT(MID(E16,SEARCH("y",E16),7),6)/0.0076358155
G17	=RIGHT(MID(E17,SEARCH("y",E17),7),6)/0.0076358155