I am in need of a formula to parse out a string of text.
Here is what I am trying to do:
In A1 I have: "a.b.c. red blue green a.b.c. green yellow
a.b.c orange blue green"
'a.b.c' or 'a.b.c.'? Looks like you want either. That makes this sort of thing
MUCH HARDER. I'll assume you mean 'a.b.c' and simply forgot the final period in
the 3rd instance above.
I would like to split out the information between
the "a.b.c" so that they are in separate columns. The
desired results would be something like: B1="red blue
green" C1="green yellow" D1="orange blue green"
If you'd *ALWAYS* have an instance of the separator string ("a.b.c.") at the
beginning of the record to be parsed, try
B1:
=MID(A1,LEN("a.b.c.")+1,FIND("a.b.c.",A1,LEN("a.b.c.")+1)-LEN("a.b.c.")-1)
C1:
=MID($A1,FIND("a.b.c.",$A1,SUMPRODUCT(LEN($B1:B1)+LEN("a.b.c."))+1)
+LEN("a.b.c."),IF((LEN($A1)-LEN(SUBSTITUTE($A1,"a.b.c.","")))
/LEN("a.b.c.")>COUNTA($B1:B1)+1,FIND("a.b.c.",$A1,SUMPRODUCT(LEN($B1:B1)
+LEN("a.b.c."))+LEN("a.b.c.")+1)-FIND("a.b.c.",$A1,SUMPRODUCT(LEN($B1:B1)
+LEN("a.b.c."))+1)-LEN("a.b.c.")-1,255))
Fill C1 right as far as needed.
This sort of thing would be MUCH EASIER using either an add-in such as Laurent
Longre's MOREFUNC.XLL, which is available at
http://longre.free.fr/english/
It provides functions named WMID and WORDCOUNT that would do this much more
simply. Alternatively, since Data > Text to columns only works with single
character field delimiters, you could select the range of these records, run
Edit > Replace, replacing 'a.b.c.' with some other character (maybe ^ or |),
then using Data > Text to columns, Delimited, and using this character as the
field delimiter.
