extract string

[owl527]

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.

[Gary Keramidas]

if i interpret your request correctly, i think this is what you need

=MID(A1,LEN(A1)-5,4)

--


Gary


"owl527" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Gary Keramidas]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Nigel]

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
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>