Using the Mid function.
 

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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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)


--
HTH

Bob Phillips

"brookdale" wrote
in message ...

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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile:
http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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


"brookdale" wrote:


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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

=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.
:)


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

=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)


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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

"brookdale" wrote:


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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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...


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555

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


"brookdale" wrote
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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile:
http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382555