Start a new topic

Adding hyphen or ignoring it

Hello! i wonder if this is possible... and I hope it is!


I get the value for my import: 089300 (or any 6 digits). This in the database is the sort code with the format: 08-93-00. I could remove it from the replace excel that I paste for the dictionary and that would work (it replaces with the bank name).


However, it'd enter the sort code wrong. So I need to add an hyphen to it when inputting on the sort code... 2 characters and then 4 should have a hyphen added.... 


Something like: 000000 would become 00-00-00. There are thousands of possible combinations so it'd need to be done automatically - probably with regex?


Many thanks!


Grazi


Hi Grazi,


I included a couple of lines to add leading zeros just in case Excel stripped them off from the beginning (better safe than sorry).


0$& ^\d{5}$ TRUE FALSE
00$& ^\d{4}$ TRUE FALSE
$1-$2-$3 ^(\d{2})(\d{2})(\d{2})$ TRUE FALSE

Thanks,


John

It worked perfectly! Thank you so much :-)

Login or Signup to post a comment