SINGLE CHARACTER DELETION

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I need to delete the first character in a field. The text
values are all in a single column and there are about 1000
rows that all have different values. They are text values.
I am wondering if there is a formula that I can write to
delete the first character of the cell or to pull out all
the characters except the first? Thanks, I need some
help.....
 
One way

=MID(TRIM(A1),2,255)

where A1 holds the value, copy down 1000 rows and paste special as values in
place..
 
keyur said:
Just out of curiousity, wht exactly happens here: LEN(A1)-
(A1<>"").
Click to expand...

When the length of A1 is non-zero, say 7, we get:

7-TRUE ==> 7-1 ==> 6

That is, TRUE is coerced into 1, when an arithmetic operator is used where
the logical result is one of the operands.

When there is nothing in A1 (that is, the cell is empty or houses a formula
returning ""), A1<>0 is FALSE, which becomes 0 by coercion.

=RIGHT(A1,LEN(A1)-1) would error out with A1 is empy or houses a formula
blank. Hence that expression instead of 1.
[...]
 
Hi
LEN(A1)-(A1<>"")
checks the following:
If A1 is blank this returns
=LEN(A1)-FALSE
=0-0 = 0

If A1 is not blank this returns
=LEN(A1)-TRUE
=LEN(A1)-1

So this comnine the check for a non blank cell with the subtraction of
1
 
Hi

Now, i have an inventory list. One of our supplier
prefixed '10' and suffixed '00' or '01' in all the num. so
now i have list with some old and some new formats. so i
want to get rid of these additions. i could take them out
one at a time(like first delete prefix and then suffix)
is it possible to get rid of both at once?

thanks
 
Do you mean?

=LEFT(RIGHT(A1,LEN(A1)-2),LEN(RIGHT(A1,LEN(A1)-2))-2)

If not, try to post some examples along with the desired results.

Hi

Now, i have an inventory list. One of our supplier
prefixed '10' and suffixed '00' or '01' in all the num. so
now i have list with some old and some new formats. so i
want to get rid of these additions. i could take them out
one at a time(like first delete prefix and then suffix)
is it possible to get rid of both at once?

thanks
Click to expand...
[...]
 
thanks a lot
-----Original Message-----
Do you mean?

=LEFT(RIGHT(A1,LEN(A1)-2),LEN(RIGHT(A1,LEN(A1)-2))-2)

If not, try to post some examples along with the desired results.

Hi

Now, i have an inventory list. One of our supplier
prefixed '10' and suffixed '00' or '01' in all the num. so
now i have list with some old and some new formats. so i
want to get rid of these additions. i could take them out
one at a time(like first delete prefix and then suffix)
is it possible to get rid of both at once?

thanks
Click to expand...
[...]


.
Click to expand...
 
Back
Top