PDA

View Full Version : SubString help



slamet Harto
08-13-2011, 12:50 AM
Hi there,

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


See attached for reference.

Thanks a bunch

Rob342
08-13-2011, 01:31 AM
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
08-13-2011, 05:10 AM
Not sure, but maybe as a UDF:

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

Paul_Hossler
08-13-2011, 07:25 AM
Assumeing that you want the next to last parameter ...


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


Paul

GTO
08-13-2011, 07:54 AM
Pennsylvania has funny spelling? Paramater almost sounds ghostly!

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

Krishna Kumar
08-13-2011, 10:10 AM
Hi,

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

Paul_Hossler
08-13-2011, 02:12 PM
Paramater almost sounds ghostly!


Could I blame my keyboard?? :dunno

Didn't think so :stars:

I'm surprised I was that close. :beerchug:

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

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

Paul

shrivallabha
08-14-2011, 06:37 AM
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)