Find number after specific letter in cell

E

enigma04

New Member
Joined
Mar 1, 2011
Messages
37
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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))
 
Upvote 0
I'm sure this can be tidied up but in vba it can be done something like this



Code: 
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
 
Last edited:
Upvote 0
Upvote 0
You must log in or register to reply here.

Similar threads

nburaq
  • Solved
Find a word combination of letters and numbers
Replies
5
Views
260
Peter_SSs
Peter_SSs
L
  • Question
Auto numbering to start at another character than the first character
Replies
3
Views
162
lmaritz
L
L
  • Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
158
lneidorf
L
F
  • Question
VBA run code based off cell value
Replies
1
Views
309
igold
igold
G
  • Question
Feet Inches Sixteenths Number Format
Replies
5
Views
644
Phuynh
P

Forum statistics

Threads
1,214,658
Messages
6,120,778
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top