Search code examples
excelparsingexcel-formulalimitmaxlength

Excel parse address character limit

I am currently in the process of parsing adresses. The only requirement I have is that there should not be more than 40 characters in each Address cell (Address 1, Address 2, Address 3 etc). The problem is that the addresses all have different formats and I want to prevent words from being cut in the middle. Example for an address (in G column): Naaaaaaaaaaaaaaaaaaaaaaaame, 342 Streeeeeet, city, 452342 Zip code, Country That´s how far I 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, however I need to find a way to perform this operation multiple times (since some addresses are really long) so that I get something like:

Address1: 40 characters

Address2: 40 characters

Address3: remaining characters

Is there any way to solve this without using VBA? I hope I am making sense and would appreciate any advice!!

Solution

  • These three progressive formula seem to work for your single example.

            Parse and Truncate Address with worksheet functions

    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 that the formula in H5 is dependent on the result in G5 and the formula in I5 is dependent on the results in H5 and G5.

    Not sure what you might want to do with the trailing comma in H5.