SubString help

[slamet Harto]

Hi there,

Please do me a favor.I want to get a part of string.


See attached for reference.

Thanks a bunch

[Rob342]

Slamet

Row 5
=Mid(A5,13,4), use same formula for the rest of the rows.

a5=cell
13 numbers of chars from the left
4 numbers of chars to extract.

This will only apply if the data remains in the same format

[GTO]

Not sure, but maybe as a UDF:

[VBA]Option Explicit

Function FindPartial(ByVal CellText) As Variant
Dim lLastSlash As Long
Dim lSlashPreceeding As Long
Dim strFrontEnd As String
Dim bolWeAreOkay As Boolean

lLastSlash = InStrRev(CellText, "/")

If CBool(lLastSlash) Then
strFrontEnd = Left(CellText, lLastSlash - 1)
lSlashPreceeding = InStrRev(strFrontEnd, "/")
If CBool(lSlashPreceeding) Then
bolWeAreOkay = True
End If
End If

If bolWeAreOkay Then
FindPartial = Mid(CellText, lSlashPreceeding + 1, Len(lLastSlash))
If IsNumeric(FindPartial) Then
FindPartial = CLng(FindPartial)
End If
Else
FindPartial = "Bad Val"
End If
End Function[/VBA]

[Paul_Hossler]

Assumeing that you want the next to last parameter ...

[VBA]
Option Explicit
Function GetNextToLastParamater(s As String) As Variant
Dim v As Variant

On Error GoTo ReturnNA

v = Split(s, "/")

GetNextToLastParamater = v(UBound(v) - 1)

Exit Function
ReturnNA:

On Error GoTo 0
GetNextToLastParamater = CVErr(xlErrNA)
End Function
[/VBA]

Paul

[GTO]

Pennsylvania has funny spelling? Paramater almost sounds ghostly!

A tease of course, that is tons nicer Paul :-)

[Krishna Kumar]

Hi,

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A5,"/"&TRIM(RIGHT(SUBSTITUTE(A5,"/",REPT(" ",100)),100)),""),"/",REPT(" ",100)),100))

[Paul_Hossler]

Paramater almost sounds ghostly!

Could I blame my keyboard??

Didn't think so

I'm surprised I was that close.

That and ParamArray (ParmArray ???) always cause me problems

Would you believe I actually edited that little post 3 times ????

Paul

[shrivallabha]

I noticed that last character set is normally:
MMMYY\NNNN\NNNN or NNNNN
With given data, following formula shall also work fine. In Cell B5

=MID(A5,FIND("/",A5,LEN(A5)-14)+1,4)