Formula to get Number out of the tag

I'll put him on hajar jahanam mesir US President Barack Obama may have won the battle of the budget (for now), but Obamacare still ignites political passions like no other piece of legislation in present-day Washington. It was never meant to be that way. The opposite is true: Obamacare, as is it widely known, is supposed to be Obamaâ??s signature legislative achievement, something many of his predecessors since Teddy Roosevelt more than hundred years ago have in vain tried to accomplish: a European-style health care law that covers almost the entire population. Congress passed the Affordable Care Act, as it is officially known, in 2009 against furious opposition of Republicans who still believe that Obamacare is, well, European-style and therefore something coming out of the Marxist-Socialist toolbox.

  Devbrat Tripathi

Hi, here is one more VBA UDF solution

Function EXTRACTHASNUMS(RefCell As String) As Long


While InStr(1, RefCell, "<") > 0


iStart = InStr(1, RefCell, "<")


iEnd = InStr(RefCell, ">")


StrRep = Mid(RefCell, iStart, iEnd - iStart + 1)


RefCell = WorksheetFunction.Substitute(RefCell, StrRep, "")


Wend


   EXTRACTHASNUMS = RefCell


End Function




  Asheesh Mattoo       06 Aug 2016       1       0     

For example, use

[B45] =MyExtract(A45, 2, "B", " ")

Function MyExtract(MyText As String, ItemNo As Integer, FrontOrBack As String, Optional MySeparator As String) As String

' This function will scan a string containing at least TWO words, each separated
' by a specified character and extract one of the words. It can extract the word
' (or SubString) counting from either the Back or Front of the text.
' [www.meadinkent.co.uk]
Dim LenText As Integer, n As Integer, CountSpaces As Integer
Dim MySt As Integer, MyFin As Integer, MyStep As Integer, Mk1 As Integer, Mk2 As Integer

' MySeparator was an optional parameter
If Len(MySeparator) = 0 Then MySeparator = " "

LenText = Len(MyText)
' You cannot extract a word if length is LT 3 chars
If LenText < 3 Then
MyExtract = "*"
GoTo MyEndBit
End If

' set the direction in which the text is examined
If UCase(FrontOrBack) = "F" Then
MySt = 2
MyFin = LenText - 1
MyStep = 1
Else
MyFin = 2
MySt = LenText - 1
MyStep = -1
End If

' identify the position of characters matching the separator
For n = MySt To MyFin Step MyStep
If Mid(MyText, n, 1) = MySeparator Then
CountSpaces = CountSpaces + 1
If CountSpaces = ItemNo - 1 Then Mk1 = n
If CountSpaces = ItemNo Then Mk2 = n
End If
Next n

If CountSpaces = 0 Then
MyExtract = "*"
GoTo MyEndBit
End If

If UCase(FrontOrBack) = "B" Then
n = Mk1
Mk1 = Mk2
Mk2 = n
End If

If Mk2 = 0 Then Mk2 = LenText + 1
Mk1 = Mk1 + 1

MyExtract = Mid(MyText, Mk1, Mk2 - Mk1)
MyEndBit:
End Function

  CA Vikram Shankar Mathur       31 Jul 2016       0       1     

Thank you for providing the solution of my question.

Finally i got the answer of my question through formula as well. you can use below mentioned formula to solve the problem-

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B1,FIND("EventProductId",B1)-1,LEN(B1)),"""EventProductId""",""),"</object></collection>",""),"value",""),"field",""),"/",""),">",""),"name=",""),"<",""),"s","")),"No Product")


Thanks...


  Devbrat Tripathi       08 Aug 2016       0       1     

Thank you for providing the solution of my question.

Finally i got the answer of my question through formula as well. you can use below mentioned formula to solve the problem-

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B1,FIND("EventProductId",B1)-1,LEN(B1)),"""EventProductId""",""),"</object></collection>",""),"value",""),"field",""),"/",""),">",""),"name=",""),"<",""),"s","")),"No Product")


Thanks...


  Devbrat Tripathi       08 Aug 2016       0       0     

Post Your Answers:

Please use the CODE HIGHLIGHT Button to format/highlight your codes if any