0

what is the easiest way with an Excel formula to extract certain details from a cell? So for example, if this is in cell A1 column=""HMI_LOCATE"" px=""CLASS"" position=""99"" validation=""ROOM"" then I'm trying to extract just the data the falls in between the double "" after the px= so in this example, I need to extract just the letters CLASS and nothing else, what is the easiest way to extract that data, the part I'm trying to extract won't always be 5 characters long it could be much longer or shorter.

rnmuk
  • 53
  • 5

1 Answers1

2

Do you want to achieve this?

enter image description here

With o365 you can use this formula

=FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(34)&CHAR(34),"</s><s>")&"</s></t>","//s[position() mod 2 = 0]")

or for older EXCEL-versions

=IFERROR(INDEX(FILTERXML("<t><s>"&SUBSTITUTE($A$1,CHAR(34)&CHAR(34),"</s><s>")&"</s></t>","//s"),ROW(A1)*2),"-")

This splits the string at the quotation marks (CHAR(34)) and builds an array of elements. Then every second element is put out.

For tons of other possibilities have a look at this awesome guide by JvdV.

EDIT: To get the element after px= no matter where it is, you can use

=LET(list,
   FILTERXML("<t><s>"&SUBSTITUTE($A$1,CHAR(34)&CHAR(34),"</s><s>")&"</s></t>","//s"),
   INDEX(list,MATCH("px=",list,0)+1)
 )

The LET-function lets you assign functions to variables which then can be used for further calculations.

enter image description here

OverflowStacker
  • 1,340
  • 1
  • 10
  • 17
  • Thank you OverflowStacker for that, just tried it and yes I see the way it splits it up, never seen this view before but I am using Excel 365 now. However, really I'm only interested in the CLASS part not the other 3 parts in this case, can they be removed so it only displays CLASS? Also, px may not always be the 2nd part of the filter that I need if px was in the third section if that makes sense? – rnmuk Nov 21 '21 at 18:18
  • Always nice to see an fellow FILTERXML enthusiast. You may alternatively drop LET and use xpath to return the value from wherever it is in the string. – JvdV Nov 21 '21 at 18:50
  • 1
    For example: `=FILTERXML(""&SUBSTITUTE(" "&A1,"""""","")&"","//s[.=' px=']/following::s[1]")`. – JvdV Nov 21 '21 at 18:58
  • 1
    Yeah, there is a fitting XPATH-command, but could not get it to work. If they were enumerated, i could tell you the number +wink+ (no offense). I could do it if you want. – OverflowStacker Nov 21 '21 at 19:00
  • @rnmuk Glad i could help. You can mark it as answer then. Be sure to check out the suggestion of JvdV. – OverflowStacker Nov 21 '21 at 19:21