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

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

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

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