i in process of parsing adresses. requirement have there should not more 40 characters in each address cell (address 1, address 2, address 3 etc). problem addresses have different formats , want prevent words being cut in middle. example address (in g column): naaaaaaaaaaaaaaaaaaaaaaaame, 342 streeeeeet, city, 452342 zip code, country that´s how far got:
adress3=if(len(g3)>40;right(g3;len(g3)-find("*";substitute(g3;" ";"*";len(g3)-len(substitute(g3;" ";"")))));"") adress1==if(i3<>""; if(right(if(i3<>"";trim(substitute(g3; i3; "")); g3);1)=",";left(if(i3<>"";trim(substitute(g3; i3; "")); g3);len(if(i3<>"";trim(substitute(g3; i3; "")); g3))-1);if(i3<>"";trim(substitute(g3; i3; "")); g3)); if(i3<>"";trim(substitute(g3; i3; "")); g3)) this works, need find way perform operation multiple times (since addresses long) like:
address1: 40 characters
address2: 40 characters
address3: remaining characters
is there way solve without using vba? hope making sense , appreciate advice!!
these 3 progressive formula seem work single example.
the formulas in g5:k5 are:
=trim(left(substitute(g3, " ", rept(" ", 99), 40-len(substitute(left(g3, 40), " ", ""))), 40)) =iferror(trim(left(substitute(mid(g3, len(g5&rept(" ", 1)), 40), " ", rept(" ", 99), 40-len(substitute(left(mid(g3, len(g5&rept(" ", 1)), 40), 40), " ", ""))), 40)), "") =iferror(trim(left(substitute(mid(g3, len(g5&h5&rept(" ", 2)), 40), " ", rept(" ", 99), 40-len(substitute(left(mid(g3, len(g5&h5&rept(" ", 2)), 40), 40), " ", ""))), 40)), "") =iferror(trim(left(substitute(mid(g3, len(g5&h5&i5&rept(" ", 3)), 40), " ", rept(" ", 99), 40-len(substitute(left(mid(g3, len(g5&h5&i5&rept(" ", 3)), 40), 40), " ", ""))), 40)), "") =iferror(trim(left(substitute(mid(g3, len(g5&h5&i5&j5&rept(" ", 4)), 40), " ", rept(" ", 99), 40-len(substitute(left(mid(g3, len(g5&h5&i5&j5&rept(" ", 4)), 40), 40), " ", ""))), 40)), "") note formula in h5 dependent on result in g5 , formula in i5 dependent on results in h5 , g5.
not sure might want trailing comma in h5.
Comments
Post a Comment