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.

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

1 Answer 1

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 :)

3
  • =IF(ISERROR(VALUE(MID(A1,2,1))),LEFT(A1,2),LEFT(A1,1))
    – Hannu
    Feb 15, 2020 at 18:36
  • yeah, combined :) I still do the recursive thing of problem solving, divide and conquer.
    – arteh
    Feb 16, 2020 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, 2020 at 11:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .