Formula to get Number out of the tag

Hi,

Please solve the attached query and get the output as mentioned in the attachment.

Question: We have html tag here, and there are numbers like 1, 5, 12, 6 etc which is not in tag or in "<....>", please extract those number from a formula with space separator.

Example:

Text:

<collection><object parentid="01f92fc2-0d9d-4542-b9e2-057aad739091" parenttype="Object"><fields><field name="EventProductId"><value>1</value></field><field name="EventProductId"><value>5</value></field><field name="EventProductId"><value>12</value></field><field name="EventProductId"><value>6</value></field></fields></object></collection>

Answer:

1 5 12 6

Thanks,




  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