0

I want to shorten postcode information in a cell to only show the first or first and second letter. i.e. WF17 would show WF only or S17 would only show S. The range of cells will have a mix of 1 character and 2 character postcodes so I would need the formula to cover both scenarios.

  • What have you already tried? We help with specific problems, but we are not a code-writing site. – harrymc Feb 14 at 17:57
0

I found a not so elegant solution.

So, if you have A1,A2 etc filled with codes that start with either 1 letter or 2 letters in another column, do the following. In B1 type

=VALUE(MID(A1,2,1))

Use autofill for the rest of the cells in B and you should have either a number or an error. Now in C1 type

=IF(ISERROR(B1),LEFT(A1,2),LEFT(A1,1))

Autofilling the rest of C should give you what you needed :)

  • =IF(ISERROR(VALUE(MID(A1,2,1))),LEFT(A1,2),LEFT(A1,1)) – Hannu Feb 15 at 18:36
  • yeah, combined :) I still do the recursive thing of problem solving, divide and conquer. – arteh Feb 16 at 20:35
  • Big thank you to H, this formula, =IF(ISERROR(VALUE(MID(A1,2,1))),LEFT(A1,2),LEFT(A1,1)), worked perfectly, much appreciate. – Jim J. Feb 17 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.