Deleting 3 Text characters from the right
 

Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

Hi

Try this:
=LEFT(A1,LEN(TRIM(A1))-3)

--
Andy.


"Helen" wrote in message
...
Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand
side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

=left(A1,FIND(" ",A1)-1)

is more resilient as I understand some postcodes only have a trailing 2.

--
HTH

Bob Phillips

"Helen" wrote in message
...
Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand
side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

Helen,

For a code in cell A1

=LEFT(A1,LEN(A1)-3)

HTH,
Bernie
MS Excel MVP


"Helen" wrote in message
...
Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand
side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

try

=LEFT(A1,LEN(A1)-3)

To be sure that you don't get fouled up by leading and trailing spaces, you
can use

=LEFT(TRIM(A1),LEN(TRIM(A1))-3)


"Helen" wrote:

Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

try

Sub trimthree()
For Each c In Selection
c.Value = Left(c, Len(c) - 3)
Next
End Sub

--
Don Guillett
SalesAid Software

"Helen" wrote in message
...
Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand
side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

try
=left(A1,len(A1)-3)

"Helen" wrote:

Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks

=LEFT(A1,(LEN(A1)-3))
should work

"Helen" wrote:

Anyone know how to trim a postcode of variable length, sometimes 5, 6 or 7
characters in length to remove the last 3 characters on the right hand side
of the cell? The postcode is displayed with any gaps or marks and the
separator will varying but it is always the last 3 characters that are
superfluous.
--
Thanks