this post was submitted on 23 Aug 2023
3 points (100.0% liked)

Excel is Cool!

84 readers
6 users here now

Prove to the world Excel is cool!

founded 1 year ago
MODERATORS
[email protected]
3
How do I extract text between two of the same characters on a string, given that said character has appeared before? (lemmy.ml)
submitted 1 year ago by [email protected] to c/[email protected]
2 comments fedilink hide all child comments
 

I'm trying to extract the diameter here, so for example from the strings

1234-AB-001-4"-X01 and 1234-AB-002-1.1/2"-X01

how do I exctract the 4" and 1.1/2"?

top 2 comments
sorted by: hot top controversial new old
[โ€“] [email protected] 4 points 1 year ago* (last edited 1 year ago) (1 children)

See if this does it for you: =TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),3*LEN(A1)+1,LEN(A1)))

This is assuming A1 is where the string is. The 3 in 3*LEN(A1) is what determines which string will be extracted, like so:

0  ->  1234
1  ->  AB
2  ->  001
3  ->  4"
4  ->  X01

Source: https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml

[โ€“] [email protected] 2 points 1 year ago

It worked! Thanks so much, I was going crazy over this.