Macro to find the string before special character from Right

[krishhi]

Hi there,

I have a problem, I want a string from the right side before the special character.

Like In "A" I have this number: 1251_2546_45875_2546
I want 2546 i.e. after "_" (last happened)

Any Clues?

Thanks in Advance,
krrish

[MaximS]

hi kirishhi,

copy/paste below into new macro module:


Private Function Splt(ByVal Txt As String, ByVal Separator As String) As String
Dim x As Variant
x = Split(Txt, Separator, -1, vbTextCompare)
Splt = x(UBound(x))
End Function


to use type in Excel:

=Splt(A1, "_")

[Simon Lloyd]

A vba solution would beFunction cEnd(cell As Range, DelChar As String) As String
Application.Calculation = xlCalculationManual
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = DelChar Then
cEnd = Mid(cell, i + 1, 99)
Exit Function
End If
Next i
cEnd = cell
Application.Calculation = xlCalculationAutomatic
End Functionand is used like this
=cEnd(A1,"_") this would find the last _ in A1 and return the rest of the cell after that.

But if you want a cell formula you can use this
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"_","*",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))) which finds the last _ and returns the rest of the cell.

[krishhi]

hi kirishhi,

copy/paste below into new macro module:


Private Function Splt(ByVal Txt As String, ByVal Separator As String) As String
Dim x As Variant
x = Split(Txt, Separator, -1, vbTextCompare)
Splt = x(UBound(x))
End Function


to use type in Excel:

=Splt(A1, "_")


Hey,
Can you explain this for me. I didn't get the actuall process here

[GTO]

Hi Krishhi,

You'll want to look up Split and Ubound in vba help. In short, MaximS' function attempts to split the cell's value using whatever delimiter (the underscore in your case), and the return values to different elements in the array 'x'. Then by using UBound, the last element is the function's return.

Awfully nice IMO, as even if there is no delimiter found, it still works, as Split returns a 0 to 0 array.

BTW, how many cells/values are we looking at and how often is this done/needed?

Mark