Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
#5
|
|||
|
|||
=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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |