Deleting 3 Text characters from the right

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
=left(A1,FIND(" ",A1)-1)

is more resilient as I understand some postcodes only have a trailing 2.
 
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)
 
try

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract characters from Right of string 5
Help required with cut and paste macro. 1
Remove Characters Until a Specific Character is Reached 7
Remove certain characters from a text string 6
HOW CAN I EXTRACT TEXT TO THE RIGHT OF A THIRD SPACE FROM THE LEFT 11
Extracting text from text string 3
chop off last 5 characters of a string 9
How to extract text from number/text cell 6

Back
Top