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

 

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