Have a Question?

If you have any question you can ask below or enter what you are looking for!



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       0       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       0     

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