CODEWITHSUNDEEP
excelexcel-formula
user1117723
user1117723

Reputation: 153

Excel formula to extract a string

Could someone please give me the excel formula to extract the string between the user tags:

  <user>secAuthority=default</user>

I need the string secAuthority=default as output

Upvotes: 0

Views: 70

Answers (4)

teylyn
teylyn

Reputation: 35990

And let's not forget Flash Fill, available in XL 2013 and up. Type the first value. Type the second value. In some cases you may need to type a third value, but then Excel will come up with a suggestion in light grey. You can make corrections and it will apply the new rules.

This is perfect for one-offs where you don't want to spend two hours to automate a job that would take a minute manually.

enter image description here

Upvotes: 0

Dan Donoghue
Dan Donoghue

Reputation: 6216

Just substitute it out

=SUBSTITUTE(SUBSTITUTE(A1,"</user>",""),"<user>","")

Or, being we know <user> is 6 chars long and </user> is 7 we could do

=MID(A1,7,LEN(A1)-13)

And finally, just playing around to make it work regardless of what is in the triangular brackets.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"</",REPT(" ",LEN(A1))),">",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

Just to put my comment in an answer:

something like =MID(A1,FIND(">",A1)+1,FIND("</",A1)-FIND(">",A1)-1)

Works for all strings like <***>Some Text</***>.

Upvotes: -1

Noam Hacker
Noam Hacker

Reputation: 4835

you can mix the FIND, RIGHT, and LEFT functions to make a substring function - knowing that you want what comes after the first ">" up to the second "<"

Upvotes: 0

Related Questions