Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract string
Hello, how do I pick up a cell (with strings) and extract 4 characters from the 5th character from right side of the string. What function can I use? Please let me know. Thanks. -- owl527 ------------------------------------------------------------------------ owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=482103 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract string
if i interpret your request correctly, i think this is what you need
=MID(A1,LEN(A1)-5,4) -- Gary "owl527" wrote in message ... Hello, how do I pick up a cell (with strings) and extract 4 characters from the 5th character from right side of the string. What function can I use? Please let me know. Thanks. -- owl527 ------------------------------------------------------------------------ owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=482103 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract string
looking at your request again, i'm not sure what you want. if there are 10
characters, and you want the 5th from the right, you would want the 6th, 7th, 8th and 9th characters. this would do that =MID(A1,LEN(A1)-4,4) -- Gary "owl527" wrote in message ... Hello, how do I pick up a cell (with strings) and extract 4 characters from the 5th character from right side of the string. What function can I use? Please let me know. Thanks. -- owl527 ------------------------------------------------------------------------ owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=482103 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract string
Building on Gary Keramidas suggestion I would also add a trim function to
the cell as so often you find spaces are at the end of strings (not visible) and using the length function will be mis-interpreted. =MID(TRIM(A1),LEN(TRIM(A1))-4,4) The other issue you face is that if the cell is less than 5 chars in length - you will get an invalid #VALUE! So a conditional check in the function will help avoid that and give you chance to show a controlled message eg =IF(LEN(TRIM(A1))<5,"Too Short",MID(TRIM(A1),LEN(TRIM(A1))-4,4)) -- Cheers Nigel "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... looking at your request again, i'm not sure what you want. if there are 10 characters, and you want the 5th from the right, you would want the 6th, 7th, 8th and 9th characters. this would do that =MID(A1,LEN(A1)-4,4) -- Gary "owl527" wrote in message ... Hello, how do I pick up a cell (with strings) and extract 4 characters from the 5th character from right side of the string. What function can I use? Please let me know. Thanks. -- owl527 ------------------------------------------------------------------------ owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=482103 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract value from a text string | Excel Worksheet Functions | |||
Extract from string | Excel Discussion (Misc queries) | |||
Extract text from String | Excel Worksheet Functions | |||
Extract sub string | Excel Worksheet Functions | |||
extract string | Excel Programming |