VBA Function to replace an excel formula

**rakeshplb** · 12-30-2011, 02:34 AM

Hi Everyone

I am struggling to convert the following excel formula to a VBA function -

=IF(ISERROR(SEARCH("-",A1)),IF(ISERROR(SEARCH(" ",A1)),A1,LEFT(A1,SEARCH(" ",A1)-1)),LEFT(A1,SEARCH("-",A1)-1))

The formula refers to the same cell 7 times, so I wanted this to simplify it and replace with a VBA function. Basically the formula works on a string to find whether it has a space or hyphen. If the string has neither a space or hyphen then the function should return the string unchanged. If the string has either a space or hyphen, the characters left-side to the first space or hyphen should be returned. That's it!

The excel formula above does this correctly. I just want this to convert to a VBA Custom Function. The attached excel file has a couple of failed attempts to get the function to work.

Appreciate your help.

Rakesh

**JieJenn** · 12-30-2011, 02:57 AM

Try this

Function NumOnly(X) As Double
Dim i As Integer
    For i = 1 To Len(X)
        If Mid(X, i, 1) = " " Or Mid(X, i, 1) = "-" Then
           NumOnly = Left(X, i - 1)
        End If
    Next i

    If NumOnly = 0 Then NumOnly = X
    
End Function

**Steffen Thomsen** · 12-30-2011, 03:04 AM

Hi,

You can use this function

Function testString(inputStr As String)
    firstSpace = InStr(inputStr, " ")
    firstHyphen = InStr(inputStr, "-")
    If firstSpace > firstHyphen And Not firstHyphen = 0 Then
        splitNum = firstHyphen
    Else
        splitNum = firstSpace
    End If
    testString = Right(inputStr, Len(inputStr) - splitNum)
End Function

**rakeshplb** · 12-30-2011, 03:05 AM

Thanks JieJenn, the function works for all the entries in the column, except for something like "123 X-Y", the function returns "#VALUE". Could you look into it, please?

**rakeshplb** · 12-30-2011, 03:11 AM

Originally Posted by Steffen Thomsen

Hi,

You can use this function

Function testString(inputStr As String)
    firstSpace = InStr(inputStr, " ")
    firstHyphen = InStr(inputStr, "-")
    If firstSpace > firstHyphen And Not firstHyphen = 0 Then
        splitNum = firstHyphen
    Else
        splitNum = firstSpace
    End If
    testString = Right(inputStr, Len(inputStr) - splitNum)
End Function

Thanks Steffen, but your function does not work for "123-YY", "123 Y-Y" and "123 YY". the expected outcomes are "123" in all these cases. Could you relook?

Thanks.

**JieJenn** · 12-30-2011, 03:17 AM

Try the array formula. I think it is a better solution. Enter with Ctrl + Shift + Enter

=LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:1024")),1)),ROW((INDIRECT("1:1024"))))))

**rakeshplb** · 12-30-2011, 03:22 AM

Originally Posted by JieJenn

Try the array formula. I think it is a better solution. Enter with Ctrl + Shift + Enter

=LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:1024")),1)),ROW((INDIRECT("1:1024"))))))

Dear JieJenn, I am looking for a VBA Function, I got an excel formula working already. could you adjust your code a bit for me?

**JieJenn** · 12-30-2011, 03:26 AM

Your formula does not work with 1234 X-Y as you described in your post above. And I am kinda lazy to rewrite the array into a UDF........ I am sure others will be glad to jump in.