Function to find value in string

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi can someone help with this funtion
if the cell selected as the input for the function is 123'150
then I would expect this to return 150
Public Function rev32(Price As String)
'=RIGHT(J5,LEN(J5)-FIND("'",J5))
rev32 = Right(Price, Len(Price) - InStr(Price, "'"))

End Function

I get a REF error
 
Works for me. Check whether you have placed the function in the proper
place..From workbook launch VBE using Alt+F11. From menu Insert a Module and
paste the below function.Close and get back to workbook and try the below
formula. Make sure the module is within the same workbook

Public Function rev32(Price As String)
rev32 = Right(Price, Len(Price) - InStr(Price, "'"))
End Function

Alternatively you can try
rev32 = Mid(Price, InStr(Price, "'")+1)
OR
rev32 = Split(Price, "'")(1)


If this post helps click Yes
 
wow most annoying thing ever... tried in new workbook, closing excel,
strange... I have no idea if it works for you...
 
If you happen to use Excel2007 (or even 2010), then REV32 is not a valid
function name, because it is a cell address.
If you change the function name you'll see your function working as
expected.
 
thank you for the help :)

Niek Otten said:
If you happen to use Excel2007 (or even 2010), then REV32 is not a valid
function name, because it is a cell address.
If you change the function name you'll see your function working as
expected.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Click to expand...
 
Yes as Niek mentioned i tried this in 2003...and hence working..It is
considered as a good programming practice to name your funcitons and
procedures to reflect what they do..like

GetValuefromString()
 
Back
Top