Formula for Extracting Character Strings
Sharlene England
I has a lengthy character field, which contains multiple words. I need to
split this lengthy string into 15 character fields. Not splitting any words
in the middle. Need formula for up to column E (5 columns).
Examples:
The quick brown fox jumped over the lazy dog.
You never know what can happen in May.
April showers bring May flowers.
ColumnA ColumnB ColumnC
The quick brown fox jumped over the lazy dog.
April showers bring May flowers.
You never know what can happen in May.
Thanks in advance for the help.
Bob Umlas
Assume the above sentences occupy A1:A3
In A5, array-enter:
=IF(ISNA(MATCH(" ",MID(A1&REPT(" ",250),{15,16},1),0)),MID(A1&REPT("
",250),1,16-MATCH(" ",MID(A1&REPT("
",250),16-ROW($1:$15),1),0)),MID(A1&REPT(" ",250),1,15))
In B5, array-enter:
=IF(ISNA(MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5,""),{15,16},1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5,""),1,16-MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5,""),16-ROW($1:$15),1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5,""),1,15))
in C5, array-enter:
=IF(ISNA(MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5&B5,""),{15,16},1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5&B5,""),1,16-MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5&B5,""),16-ROW($1:$15),1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5&B5,""),1,15))
in D5, array-enter:
=IF(ISNA(MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5,""),{15,16},1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5,""),1,16-MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5,""),16-ROW($1:$15),1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5,""),1,15))
and in E5, array-enter:
=IF(ISNA(MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5&D5,""),{15,16},1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5&D5,""),1,16-MATCH(" ",MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5&D5,""),16-ROW($1:$15),1),0)),MID(SUBSTITUTE(A1&REPT("
",250),A5&B5&C5&D5,""),1,15))
Then select A5:E5 and fill down 3 rows.
Thanks for the challenge! (Took about 10 minutes). There might be a
shorter way (David Hager--you out there?), but this is what I came up
with in the time I had -- got to get back to work.
In my spare time, I may tackle this again!
P.S. To array-enter a formula, hold ctrl/shift while pressing Enter.
Nick Hodge
Bob
Ten minutes....... well I give up <g>
--
HTH
Nick Hodge
Oxford, England
nick_...@lineone.net
Bob Umlas wrote in message <3512D3...@kpmg.com>...
Laurent Longre
Bonjour Charlene,
You could try the following array formulae (Ctrl-Shift-Enter).
Assuming that your string is in A1 and that you want to split it in the
cells A5 to C5:
- In A5 :
=LEFT(A1,17-MATCH(" ",MID(A1&" ",17-ROW(INDIRECT("1:16")),1),0))
- In B5 :
=LEFT(MID(A1,LEN(A5)+1,16),17-MATCH(" ",
MID(MID(A1&" ",LEN(A5)+1,16),17-ROW(INDIRECT("1:16")),1),0))
- In C5:
=LEFT(MID(A1,LEN(A5&B5)+1,16),17-MATCH(" ",
MID(MID(A1&" ",LEN(A5&B5)+1,16),17-ROW(INDIRECT("1:16")),1),0))
Hope this helps,
Laurent
Bob Umlas
Nice formula -- but you don't need the ROW(INDIRECT("1:16")), you could
just use ROW(1:16)
Chip Pearson
I'll dive in here (even if the rocks are just below the water).
Laurent: ROW(INDIRECT("1:16"))
Bob: ROW(1:16)
Laurent: No
Chip: ROW($1:$16)
Wouldn't Bob's solution work just fine if we use absolute referencing here.
Just a thought.
Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm
Laurent Longre wrote in message <351711...@wanadoo.fr>...
>Hi Bob,
>
>If you use ROW(1:20), the formula is conditioned by this particular
>range. i.e, if you insert later one row before row 1:1 (why not?), it
>will become ROW(2:21) and the formula won't work properly. Any action
>like inserting, deleting or moving rows in the range 1:20 will change
>the initial expression and make the formula fail.
>
>I always use expressions like ROW(INDIRECT("1:N")) in order to avoid
>this risk. It will *always* return a series of integers 1...N, even if
>the "physical" range 1:N has been modified by the user.
>
>Laurent
Chip Pearson
Well, Laurent,
You're right. I'm wrong. I should have known better that to dive in here.
Your posts to these newsgroups are beyond reproach. I knew that I looked
too easy to be true.
Cordially,
Chip Pearson
Laurent Longre wrote in message <351726...@wanadoo.fr>...
>Chip,
>
>Absolute and relative references are the same regarding this problem.
>
>A short example :
>
>Form 1 : {=ROW(1:10)} = 1,2,...,10
>Form 2 : {=ROW($1:$10)} = 1,2,...,10
>Form 3 : {=ROW(INDIRECT("1:10"))} = 1,2,...,10
>
>Now insert a row before 1:1
>
>Form 1 is now {=ROW(2:11)} = 2,3,...,11
>Form 2 is now {=ROW($2:$11)} = 2,3,...,11
>Form 3 is still {=ROW(INDIRECT("1:10"))} = 1,2,...,10
>
>And if you suppress the 11 first rows, the two first formulas will
>become... {=ROW(#REF!)}! Not the third, which will stay unchanged till
>doomsday.
>
>Amicalement,
>
>Laurent
Laurent Longre
Hi Bob,
If you use ROW(1:20), the formula is conditioned by this particular
range. i.e, if you insert later one row before row 1:1 (why not?), it
will become ROW(2:21) and the formula won't work properly. Any action
like inserting, deleting or moving rows in the range 1:20 will change
the initial expression and make the formula fail.
I always use expressions like ROW(INDIRECT("1:N")) in order to avoid
this risk. It will *always* return a series of integers 1...N, even if
the "physical" range 1:N has been modified by the user.
Laurent
Laurent Longre
Chip,
A short example :
Amicalement,
Laurent