Function to find value in string

[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

 

[Jacob Skaria]

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

 

[John]

wow most annoying thing ever... tried in new workbook, closing excel,
strange... I have no idea if it works for you...

 

[Niek Otten]

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.

 

[John]

thank you for the help

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

 

[Jacob Skaria]

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()