Extract variable number of chars from variable start position?

  • Thread starter Thread starter Ann Scharpf
  • Start date Start date
A

Ann Scharpf

I am working with a worksheet that has a column of program
numbers. Some cells contain a single number; others
contain a start and end number. In between, there may be
many rows that don't have any valid data at all:

jams200c
bills
20040202
171307
jams200j-TO-jams200c
jadumpstart
jadifmsoff-TO-jadumpstart

I need to ignore all cells that don't start with "ja",
then split this information into two separate columns as
follows:

Begin End
jams200c
jams200j jams200c
jadumpstart
jadifmsoff jadumpstart

I have figured out how to combine the FIND and MID
functions to identify the Begin and End program numbers.
My problem is that the method I'm using only allows me to
grab a fixed number of characters:

=IF(ISERR(FIND("ja",Description)),"None",MID
(Description,FIND("ja",Description,1),8))

=IF(ISERR(FIND("ja",Description,4)),"None",MID
(Description,FIND("ja",Description,4),8))

Is there a method I can use to grab the WHOLE WORD that
contains the "ja" at the beginning?

Thanks for any help you can give me.

Ann Scharpf
 
Hi
try the following formulas:
1. For the start part:
=IF(LEFT(A1,2)="ja";LEFT(A1,IF(ISERROR(FIND("-",A1)),255,FIND("-",A1)-1
)),"")

2. For the end part:
=IF(LEFT(A1,2)="ja";IF(ISERROR(FIND("TO-",A1)),"",MID(A1,FIND("TO-",A1)
+3,255));"")

These work at least for your sample data
 
...
...
I have figured out how to combine the FIND and MID
functions to identify the Begin and End program numbers.
My problem is that the method I'm using only allows me to
grab a fixed number of characters:

=IF(ISERR(FIND("ja",Description)),"None",MID
(Description,FIND("ja",Description,1),8))

=IF(ISERR(FIND("ja",Description,4)),"None",MID
(Description,FIND("ja",Description,4),8))

Is there a method I can use to grab the WHOLE WORD that
contains the "ja" at the beginning?
Click to expand...

First, you may find it easier in the long run to have no match return either ""
or FALSE rather than "None". Also, if you want to process only entries beginning
with "ja", you shouldn't use FIND. You should just check if the first two chars
are "ja". Finally, if begin/end "ja" entries are always separated by "-TO-" and
everything after "-TO-" is part of the end portion, search for "-TO-ja",
otherwise entries like "jaxyzja0-TO-jamama" could cause problems.

So I'd rewrite these as

=IF(LEFT(Description,2)="ja",IF(FIND("-TO-ja",Description&"-TO-ja")<
LEN(Description),LEFT(Description,FIND("-TO-ja",Description)-1),Description),"")

=IF(FIND("-TO-ja",Description&"-TO-ja")<LEN(Description),MID(Description,
FIND("-TO-ja",Description)+4,LEN(Description)),"")

where I'm representing no match as "" rather than "None".
 
Lazy way:-

1) Sort your data in ascending order and delete all the first rows that don't
begin with ja - (They will all now be grouped together). If you don't care
about these then just skip this step.
2) Select what is left and do Edit / Replace, replacing -TO- with %%
3) Select what is left and do Data / Text To Columns / Delimited / Tick Other
and put % in the box. Make sure that 'Treat consecutive delimiters as one' is
checked. Hit OK

Job Done
 
Thanks for your responses, Frank & Harlan.

I ended up using the first of Frank's equations. I had
already figured out that I could successfully use this
formula for the "after" cells:

=IF(ISERR(FIND("-TO-",Description,5)),"",MID
(Description,FIND("-TO-",Description)+4,15))

I do have a couple of questions.

1. My FIND formulas were case sensitive. The IF(LEFT
function does not seem to be. The current formula is
grabbing data that looks like JACK DIFNIM TUE 2-03. I'm
looking in the help and I don't see a way to make the IF
(LEFT statement be case sensitive. Is there something I'm
missing? (I need to ignore those and also things that
have the text string "jaxville.")

2. I pasted your formula and it works like a gem ... but
I don't UNDERSTAND it! (Can't reproduce what you don't
understand.) Why am I concatenating between Description
and "-TO-ja"? I think you're comparing the length of the
substring before the (possible) "-TO-ja" to the length of
the whole string. If less, grab just what's before "-TO-
ja", if the same, grab the whole cell. But I only "know"
that because it's what I asked to do. I don't think I
could look at this out of context and understand how it
works.

Thanks SO much for your help. I've been struggling with
this all day.

Ann
 
To top-post is human, to bottom-post and snip is sublime.
.
Click to expand...

Well, I completely goofed up parts of my reply. I used
HARLAN's equation. And I neither bottom-posted nor
snipped.

Thanks for your help. Sorry I responded incorrectly.

Ann
 
-----Original Message-----
Lazy way:-

1) Sort your data in ascending order and delete all the first rows that don't
begin with ja - (They will all now be grouped together). If you don't care
about these then just skip this step.
2) Select what is left and do Edit / Replace, replacing - TO- with %%
3) Select what is left and do Data / Text To Columns / Delimited / Tick Other
and put % in the box. Make sure that 'Treat consecutive delimiters as one' is
checked. Hit OK

Job Done
Click to expand...

That would work if this was a one shot deal. I am
building templates where "schedulers" can take data dumps
from 13 systems on a daily basis and get a listing of all
the programs that got run, while skipping all the
extraneous information that comes out of the dump.

Thanks anyway.

Ann
 
LOL - Harlans sig is an automatic addendum to his notes, much the same as mine
is. It was not meant to be directed specifically at you, and is there simply
for general info and guidance.
 
...
...
1. My FIND formulas were case sensitive. The IF(LEFT
function does not seem to be. The current formula is
grabbing data that looks like JACK DIFNIM TUE 2-03. I'm
looking in the help and I don't see a way to make the IF
(LEFT statement be case sensitive. Is there something I'm
missing? (I need to ignore those and also things that
have the text string "jaxville.")
Click to expand...

If you need case sensitivity, then use EXACT(LEFT(.,2),"ja") instead of
LEFT(.,2)="ja". But a straight comparison is better than using FIND unless you
check that FIND's result is 1. Otherwise, it'll pick up records like

FUBAR! jajaja!
2. I pasted your formula and it works like a gem ... but
I don't UNDERSTAND it! (Can't reproduce what you don't
understand.) Why am I concatenating between Description
and "-TO-ja"? I think you're comparing the length of the
substring before the (possible) "-TO-ja" to the length of
the whole string. If less, grab just what's before "-TO-
ja", if the same, grab the whole cell. But I only "know"
that because it's what I asked to do. I don't think I
could look at this out of context and understand how it
works.
Click to expand...

This was in my formula, not Frank's. Appending the search text to the string
being searched avoids the need for nested function calls. The boolean result

ISNUMBER(FIND("-TO-ja",Description))

is always the same as the boolean result

FIND("-TO-ja",Description&"-TO-ja")<LEN(Description)

unless Description evaluates to an error, in which case the latter propagates
the error, which I consider a good thing in that circumstance.
 
ISNUMBER(FIND("-TO-ja",Description))
is always the same as the boolean result

FIND("-TO-ja",Description&"-TO-ja")<LEN(Description)

unless Description evaluates to an error, in which case the latter propagates
the error, which I consider a good thing in that
Click to expand...
circumstance.


Thanks for taking the time to explain!

Ann
 
Back
Top