Using INDIRECT along with Text-string functions

[BlackieHamel]

I'd like to use text string functions to retrieve 3 letters from the middle of a word, measured from x spaces in. For example, if my string is UNITEDSTATESOFAMERICA in A1, and I want to begin 5 spaces in, I can use


=MID(A1,5,3) and it returns EDS
But if I want to do this 5 spaces in from the right, my formula is
=LEFT(RIGHT(A1,5),3) and it returns ERI


This works for me. But I'd also like to be able to change this on the fly, and I think I need to use the INDIRECT function, so if a cell, say, E1 is "L" it would do the MID function and if E1 is "R" it would do the LEFT and RIGHT functions.


I despair of figuring out where the apostrophes and quote marks would go in such a statement. Can someone help?

 

[ukmikeb]

Hi

Welcome to MrExcel forum.

Despair no longer, try this :-

=MID(A1,IF(E1="L",5,LEN(A1)-4),3)

hth

 

[BlackieHamel]

Hi

Welcome to MrExcel forum.

Despair no longer, try this :-

=MID(A1,IF(E1="L",5,LEN(A1)-4),3)

hth

Thanks so much! Exactly what I needed -- and no INDIRECT needed! Sometimes you do need an IF statement....

 

[ukmikeb]

Thanks so much! Exactly what I needed -- and no INDIRECT needed! Sometimes you do need an IF statement....

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.

And just for the record you can do it without an IF :-

=MID(A1,5+(E1="R")*(LEN(A1)-9),3)

hth