=IF(TRIM(RIGHT(D4,1))="X",MID(D4,1,LEN(D4)-1),D4) ? remove error
 

plz remove error from the formula? If any one can help. My excel sheet
contains values like 561XX, I want to remove the right most X from the text.

plz remove error from the formula? If any one can help. My excel
sheet
contains values like 561XX, I want to remove the right most X from
the text.

=if(right(trim(D4);1)="X";left(D4;len(trim(D4))-1);D4)

arno

=if(right(trim(D4);1)="X";left(D4;len(trim(D4))-1);D4)

pls. use , instead of ; in the formula!
arno

Works fine for me, what do you get?

--
HTH

Bob Phillips

"Muhammad Nasir" <Muhammad wrote in message
...
plz remove error from the formula? If any one can help. My excel sheet
contains values like 561XX, I want to remove the right most X from the
text.

Hi Bob,
Works fine for me

it cannot work if you have trailing spaces.

arno

shouldnt it be =IF(TRIM(RIGHT(D4,2))="XX",MID(D4,1,LEN(D4)-1),D4) if he wants
to remove one of the two xxs??
--
paul
remove nospam for email addy!



"arno" wrote:

Hi Bob,
Works fine for me

it cannot work if you have trailing spaces.

arno

He doesn't, he specifically said just the right-most X.

--
HTH

Bob Phillips

"paul" wrote in message
...
shouldnt it be =IF(TRIM(RIGHT(D4,2))="XX",MID(D4,1,LEN(D4)-1),D4) if he
wants
to remove one of the two xxs??
--
paul
remove nospam for email addy!



"arno" wrote:

Hi Bob,
Works fine for me

it cannot work if you have trailing spaces.

arno

Hi Bob,
He doesn't, he specifically said just the right-most X.

any single X in the string is the right-most X.

So, Muhammad, pls. tell us:
- Do you have leading or trailing spaces (this would explain the
trim-function in your formula)?
- Do you want to remove an X if it is the last character of the string?
Or do you want to remove only the second X of XX at the end of the
string, or... whatever, just tell us.

arno

Hi. Bob and Arno
thanks for spending time for me. Yes I have leading and trailing spaces in
the specific column thats why i used Trim. Error always comes on ="X" part. I
also used single quotes but invain. I dont want to remove double x i'.e. xx

thnaks again

"arno" wrote:

Hi Bob,
He doesn't, he specifically said just the right-most X.

any single X in the string is the right-most X.

So, Muhammad, pls. tell us:
- Do you have leading or trailing spaces (this would explain the
trim-function in your formula)?
- Do you want to remove an X if it is the last character of the string?
Or do you want to remove only the second X of XX at the end of the
string, or... whatever, just tell us.

arno

So do you still have a problem, or was Arno's response working for you?

--
HTH

Bob Phillips

"Muhammad Nasir" <Muhammad wrote in message
...
Hi. Bob and Arno
thanks for spending time for me. Yes I have leading and trailing spaces in
the specific column thats why i used Trim. Error always comes on ="X"
part. I
also used single quotes but invain. I dont want to remove double x i'.e.
xx

thnaks again

"arno" wrote:

Hi Bob,
He doesn't, he specifically said just the right-most X.

any single X in the string is the right-most X.

So, Muhammad, pls. tell us:
- Do you have leading or trailing spaces (this would explain the
trim-function in your formula)?
- Do you want to remove an X if it is the last character of the string?
Or do you want to remove only the second X of XX at the end of the
string, or... whatever, just tell us.

arno