Hi
I have list of variable length postcodes eg BA1 1AA and BS11 2AA -I am looking for a formula that will remove the middle space in the longer postcode but not the shorter one, ie it will standardise both types to 7 characters long, so BS11 2AA becomes BS112AA, whilst BA1 1AA remains unchanged.
I seem to recall seeing a formula which uses IF SUBSTITUTE which has worked before.
Thanks
=if(len(a1)>7,substitute(a1," ",""),a1)
=if(len(a1)=7,a1,substitute(a1," ",""))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
hi David_Haycock, welcome to the forum. so your data only has 7 or 8 characters? try:
=IF(LEN(A1)=7,A1,SUBSTITUTE(A1," ",""))
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thank you all for your submissions. Problem is now resolved.
Kind regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks