MID Formula Not Delivering Required Results

[gmooney]

Hi,

I have a value in cell D2:

Retail A South Pacific TA

My formula is: =UPPER(MID(D2,10,(LEN(D2)-3)))

The result is

SOUTH PACIFIC TA

I only want SOUTH PACIFIC

I have 100's of rows with varying length of characters. This is simply one example.

Any thoughts?

 

[AhoyNC]

Assuming you are always going to start 10 characters in and drop the last 3 - try:

Book1
	D
1
2	Retail A South Pacific TA
3
4	SOUTH PACIFIC
Sheet1

Cell Formulas
Range	Formula
D4	D4	=UPPER(MID(LEFT(D2,LEN(D2)-3),10,LEN(D2)))

 

[gmooney]

Assuming you are always going to start 10 characters in and drop the last 3 - try:

Book1
	D
1
2	Retail A South Pacific TA
3
4	SOUTH PACIFIC
Sheet1

Cell Formulas
Range	Formula
D4	D4	=UPPER(MID(LEFT(D2,LEN(D2)-3),10,LEN(D2)))

Thank you! That works!