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

M

Muhammad Nasir

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.
 
A

arno

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.
Click to expand...

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

arno
 
A

arno

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

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

Bob Phillips

Works fine for me, what do you get?

--
HTH

Bob Phillips

Muhammad Nasir said:
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
Click to expand...
text.
 
P

paul

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??
 
A

arno

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

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
 
M

Muhammad Nasir

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
 
Top