Using the Mid function.

[brookdale]

Time for the next question. I know how to use the basic
mid(cell,start,amount). This always starts from the beginning of the
cell. How could I amke one of these so that it starts at the end of the
cell. In other words, so that the start part is spaces from the right
and not from the left. I have to do this because the text I am working
with is at the end of cell that are of unequal lenght.

Thanks.

 

[Bob Phillips]

This may not be what you want, as I feel you probably want a dynamic start
point, but as you didn't specify

=MID(A1,Len(A1)-8,end)

 

[bj]

use the Right() function
=Right(A1,(Number of characters you want))

 

[brookdale]

=Right(A1,(Number of characters you want))

But then that will continue to the end of the cell? What if I want to
start 8 characters from the right, but I only want to include the 8th,
7th, and 6th spaces from the right. Not the rest. I hope that is clear.

 

[anilsolipuram]

=MID(C5,LEN(C5)-(which_space-1),1)

for example you want 1st place from last than

it is

=MID(C5,LEN(C5)-(1-1),1)


for example you want 2nd place from last than

=MID(C5,LEN(C5)-(2-1),1)

 

[swatsp0p]

Then you need to use Bob Phillips formula:

=MID(A1,Len(A1)-8,end)

where 'end' is the number of characters you want returned, in your
example, 3:

=MID(A1,Len(A1)-8,3)

where [mid(cell,start,amount)] the 'start' character is determined by
the length (len) of the value in A1 minus 8 (in affect, 8 characters
from the right) and then return 3 characters. Therefore, if A1
contains "abcdefghijklmnopqrst" (20 chars), the above formula will
return: "lmn" (starting with the 12th char (20-8).

HTH

Bruce

 

[brookdale]

Thanks so much guys. It worked perfectly. Now you are going to be ma
because I have another question. Here is the equation I was using fo
something else:

=IF(ISBLANK(C2),RIGHT(K2,8),RIGHT(C2,8))

Basically I have a set of data, but it could randomly be in column C o
K. So this chose the appropriate one. Now, a few columns had nothing i
either, and this equation left a blank value, which was what I wanted.

Here is the full equation I have been asking you for help with:

=IF(ISBLANK(C2),MID(K2,LEN(K2)-15,8),MID(C2,LEN(C2)-15,8))

It also choses the appropriate column. However, if both C and K ar
blank, it returns a #VALUE!, rather than a blank. Any way to produce
blank if both C and K are empty?

Thanks for dealing with my annoying questions

 

[bj]

try
=IF(ISBLANK(C2),if(isblank(K2),"",MID(K2,LEN(K2)-15,8)),MID(C2,LEN(C2)-15,8))

 

[swatsp0p]

We need to check for an Error, and if found, return "" (an empty
string), as such:

=IF(ISERROR(IF(ISBLANK(C2),MID(K2,LEN(K2)-15,8),MID(C2,LEN(C2)-15,8))),"",IF(ISBLANK(C2),MID(K2,LEN(K2)-15,8),MID(C2,LEN(C2)-15,8)))

Good Luck

Bruce

 

[brookdale]

Wow. Thank you all again. I am not at work now )), but I will be sure
to give this a try as soon as I get in.

I am compiling a word document with a lot of these formulas so that it
can all be used in a step by step guide to complete class schedules for
the college I am working at.

I have one last formula that I am going to need help on. I am saving it
for last because it is the most complicated. I will post it tomorrow
when I get to work, as I have already typed most of the question up
there.

Till then...

 

[Dana DeLouis]

Just throwing out another idea...

=IF(AND(C2="",K2=""),"", LEFT(RIGHT(IF(C2="",K2,C2),15),8))

using this general idea:
=LEFT(RIGHT(A1,8),3)

HTH
--
Dana DeLouis
Win XP & Office 2003


in message
Thanks so much guys. It worked perfectly. Now you are going to be mad
because I have another question. Here is the equation I was using for
something else:

=IF(ISBLANK(C2),RIGHT(K2,8),RIGHT(C2,8))

Basically I have a set of data, but it could randomly be in column C or
K. So this chose the appropriate one. Now, a few columns had nothing in
either, and this equation left a blank value, which was what I wanted.

Here is the full equation I have been asking you for help with:

=IF(ISBLANK(C2),MID(K2,LEN(K2)-15,8),MID(C2,LEN(C2)-15,8))

It also choses the appropriate column. However, if both C and K are
blank, it returns a #VALUE!, rather than a blank. Any way to produce a
blank if both C and K are empty?

Thanks for dealing with my annoying questions.