MID Formula

[Gary Thomson]

Hi,

I have a text string, which is always 10 characters long.
Each character can be either "1", or any letter from "a"
through "z".

Examples:

1111111111 (each character is a "1")
11b1c11d1e (mostly 1's, with other letters)
11b11b1bbb (a recurring letter - "b")

I want to check each character in the string in turn,
starting with the character on the left, and sum
their "lookup values" (from a table in sheet 2) as follows:

If the first character is not a "1", lookup the first
character in the table in sheet 2, otherwise 0.

PLUS

If the second character is not a "1" AND is not the same
character as the first character, then lookup the second
character in the table in sheet 2, otherwise 0.

PLUS

If the third character is not a "1" AND is not the same as
the first character AND is not the same as the second
character, then lookup the third character in sheet 2,
otherwise 0.

and so on for all 10 characters, where the 10th character
would only be "looked-up" if it was not a "1" and not the
same as any of the previous 9 characters

The formula I have so far is:

=IF(MID(H481,1,1)="1",0,VLOOKUP(MID(H481,1,1),Services!
$N$10:$S$87,6,FALSE))+IF(AND(MID(H481,2,1)<>"1",MID
(H481,2,1)<>MID(H481,1,1)),VLOOKUP(MID(H481,2,1),Services!
$N$10:$S$87,6,FALSE),0)+IF(AND(MID(H481,3,1)<>"1",MID
(H481,3,1)<>MID(H481,1,1),MID(H481,3,1)<>MID
(H481,2,1)),VLOOKUP(MID(H481,3,1),Services!
$N$10:$S$87,6,FALSE),0)..........


Which is HUGE, and I need this formula in a 31 by 500
range, which has made my spreadshett 50MB.

Is there a simpler way to do this??

 

[Stephen Bye]

This formula works for me...
=IF(FIND(MID(H481,1,1),1&H481)<=1,0,VLOOKUP(MID(H481,1,1),Services!$N$10:$S$
87,6,FALSE))+IF(FIND(MID(H481,2,1),1&H481)<=2,0,VLOOKUP(MID(H481,2,1),Servic
es!$N$10:$S$87,6,FALSE))+IF(FIND(MID(H481,3,1),1&H481)<=3,0,VLOOKUP(MID(H481
,3,1),Services!$N$10:$S$87,6,FALSE))+IF(FIND(MID(H481,4,1),1&H481)<=4,0,VLOO
KUP(MID(H481,4,1),Services!$N$10:$S$87,6,FALSE))+IF(FIND(MID(H481,5,1),1&H48
1)<=5,0,VLOOKUP(MID(H481,5,1),Services!$N$10:$S$87,6,FALSE))+IF(FIND(MID(H48
1,6,1),1&H481)<=6,0,VLOOKUP(MID(H481,6,1),Services!$N$10:$S$87,6,FALSE))+IF(
FIND(MID(H481,7,1),1&H481)<=7,0,VLOOKUP(MID(H481,7,1),Services!$N$10:$S$87,6
,FALSE))+IF(FIND(MID(H481,8,1),1&H481)<=8,0,VLOOKUP(MID(H481,8,1),Services!$
N$10:$S$87,6,FALSE))+IF(FIND(MID(H481,9,1),1&H481)<=9,0,VLOOKUP(MID(H481,9,1
),Services!$N$10:$S$87,6,FALSE))+IF(FIND(MID(H481,10,1),1&H481)<=10,0,VLOOKU
P(MID(H481,10,1),Services!$N$10:$S$87,6,FALSE))
.... but you should really put it into a user-defined function which contains
a loop to check each position from 1 to 10. I'm sure someone here or at the
microsoft.public.excel.programming newsgroup can show you how to do that.
Then you can just put
=myfunction(H481)
in the cell to get the result.

 

[Gary Thomson]

Thanx Stephen. That does indeed work.

-----Original Message-----
This formula works for me...
=IF(FIND(MID(H481,1,1),1&H481)<=1,0,VLOOKUP(MID (H481,1,1),Services!$N$10:$S$
$N$10:$S$87,6,FALSE))+IF(FIND(MID(H48
(H481,7,1),Services!$N$10:$S$87,6
(H481,10,1),1&H481)<=10,0,VLOOKU
P(MID(H481,10,1),Services!$N$10:$S$87,6,FALSE))
.... but you should really put it into a user-defined function which contains
a loop to check each position from 1 to 10. I'm sure someone here or at the
microsoft.public.excel.programming newsgroup can show you how to do that.
Then you can just put
=myfunction(H481)
in the cell to get the result.




.

 

[Leo Heuser]

Hi Gary

One way, assuming data in B3 and down,
lookup value in Sheet2!G3:G29 and result value
in Sheet2!H3:H29.
In Sheet2!G3 enter '1 (single apostrophe and 1,
to make it text), and in Sheet2!H3 enter 0 (zero).
H4:H29 contains the letters.

In e.g. C3 enter this formula:

=SUM(LOOKUP(IF(LEN(LEFT(B3,ROW(INDIRECT("1:10"))))-
LEN(SUBSTITUTE(LEFT(B3,ROW(INDIRECT("1:10"))),
MID(B3,ROW(INDIRECT("1:10")),1),""))=1,MID(B3,ROW(
INDIRECT("1:10")),1),"1"),Sheet2!$G$3:$G$29,Sheet2!$H$3:$H$29))

The formula is an array formula and must be entered
with <Shift><Ctrl><Enter> instead of <Enter>, also
if edited later. If done correctly, Excel will display the
formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

Copy C3 down with the fill handle (the little square
in the lower right corner of the cell)

 

[Ron Rosenfeld]

Is there a simpler way to do this??

Probably but could you give an example of what the output should look like
given a few different inputs?

For example, the '+' implies that all the characters in the lookup table are
numeric, and that you are performing simple addition. Is that the case?

If so, it seems your algorithms would equate to adding up the LOOKUP value that
matches the first unique value in the string.

So if your table were something like

a 1
b 2
c 3
d 4

then 1aabb

would translate to 0+1+0+2+0 or 3

If that is the case, then I would set up your LOOKUP range with the values as
above. In addition, to take care of ignoring '1', I would have, as the first
row:

'1 0

Note the single quote in front of the '1' which makes this a TEXT entry. The
LOOKUPS should be in the first row, and the associated values in the last row
of the table. And the first row should be sorted.

Naming the table 'rng', the following *array-entered* formula would seem to do
what I understand you want, with a string in A1;

=LOOKUP(LEFT(A1,1),rng)+SUM(IF(ISERROR(
FIND(MID(A1,ROW(INDIRECT("2:10")),1),MID(
A1,1,ROW(INDIRECT("1:9"))))),LOOKUP(MID(
A1,ROW(INDIRECT("2:10")),1),rng),0))

To *array-enter* a formula, after copying or pasting it in, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.


--ron

 

[Stephen Bye]

Okay then, using the array formula approach suggested by Leo & Ron, here's a
shorter version:

=SUM(IF(FIND(MID(H481,ROW($A$1:$A$10),1),1&H481)<=ROW($A$1:$A$10),0,LOOKUP(M
ID(H481,ROW($A$1:$A$10),1),Services!$N$10:$N$87,Services!$S10:$S87)))

(to be entered as an array formula with Ctrl+Shift+Enter)

 

[Stephen Bye]

Sorry, I missed out a $ or 2.
The $S10:$S87 at the end should be $S$10:$S$87.

 

[Stephen Bye]

And reversing the IF test makes it shorter still:

=SUM(IF(FIND(MID(H481,ROW($A$1:$A$10),1),1&H481)>ROW($A$1:$A$10),LOOKUP(MID(
H481,ROW($A$1:$A$10),1),Services!$N$10:$N$87,Services!$S$10:$S$87)))

 

[Harlan Grove]

And reversing the IF test makes it shorter still:

=SUM(IF(FIND(MID(H481,ROW($A$1:$A$10),1),1&H481)>ROW($A$1:$A$10),LOOKUP(MID(
H481,ROW($A$1:$A$10),1),Services!$N$10:$N$87,Services!$S$10:$S$87)))

However, the OP was using VLOOKUP with 4th argument FALSE, so the table in
question isn't necessarily sorted in ascending order on the 1st column. Besides,
using VLOOKUP would make the formula shorter still.

=SUM(IF(FIND(MID(H481,ROW($1:$10),1),1&H481)>ROW($1:$10),
VLOOKUP(MID(H481,ROW($1:$10),1),Services!$N$10:$S$87,6,0)))

If the table were sorted in ascending order on col N, the formula could be
shortened still further to

=SUM(IF(FIND(MID(H481,ROW($1:$10),1),1&H481)>ROW($1:$10),
LOOKUP(MID(H481,ROW($1:$10),1),Services!$N$10:$S$87)))

 

[Stephen Bye]

Okay, I think we'd better stop there before it disappears altogether!