AW: Inhalt zwischen 2 Sonderzeichen auslesen
07.02.2017 11:00:37
Fennek
Hallo,
bei excel-fox.com gab es mal einen Ansatz:
Rick Rothstein (Fox)
Get Field from Delimited Text String
How many times have you had a delimited text string (say "one,two,three,four" where the _
delimiter is a comma) and you wanted a formula to retrieve one of the delimited values, say, the third one. Personally, I have wondered for years why such a function is not built into Excel but, alas, it isn't. We could use VB to create a UDF (user defined function), but as it turns out, that is not necessary. Here is a relatively simple formula that will parse delimited text out from within a larger text string up to 99 characters long...
=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",99)),fieldnumber*99-98,99))
You simply have to replace the word "delimiter" with a text value (one or more characters _
long) and replace the word "fieldnumber" with the number (starting at 1) for the field of text you want. For example, let's say the cell A1 has this text in it...
one,two,three,four,five,six,seven,eight,nine,ten
This formula can be used to retrieve, say, the seventh field of data...
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),7*99-98,99))
or, simplifying the expression by performing the indicated math toward the end of the _
formula...
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),595,99))
Remember, the delimiter does not have to be a single character. Let's say your this text _
was in cell A1...
one - two - three - four - five - six - seven
and you wanted, say, the third field using the dash as the delimiter. You could use "-" as _
the delimiter and then apply the TRIM function to it in order to get rid of the leading/trailing spaces that would attach to the text returned if only as single dash was used for the delimiter; however, if we use " - " (space/dash/space) as the delimiter, then the desired result would be returned directly...
=TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",99)),3*99-98,99))
mfg