Customer Address Cleanup? Split and spread into next cell.

[razzandy]

I need some code or formula to look at a cell to check the entered characters do not exceed last say 25 characters. If it does trim it down to the last full word then place the rest in the next cell along.

So if Cell A1 had: (Unit 27a 150 Biglongmassive Street) it would change it to:

Cell A1: Unit 27a 150
Cell B1: Biglongmassive Street

Hope somebody can help

Thanks in advance

 

[daniels012]

I know this is one example but are there consistency's? Something like find the last number and extract the words before and afterward?
Would that suffice?

 

[MickG]

Try this for Addresss in column "A".

[COLOR="Navy"]Sub[/COLOR] MG20Sep15
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Sp
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nTxt    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Temp = Dn
 Txt = ""
   c = 0
   Sp = Split(Temp, " ")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
    Txt = Txt & " " & Sp(n)
        [COLOR="Navy"]If[/COLOR] Len(Txt) >= 25 [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, c) = nTxt
            Txt = Sp(n)
            nTxt = ""
            c = c + 1
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Len(Txt) < 25 And n = UBound(Sp) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, c) = Txt
        [COLOR="Navy"]End[/COLOR] If
        
        nTxt = Txt
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[razzandy]

Thanks Mick I will give that a go later today.

Just to answers Daniels012: no never consistent due to Customer Input. A lot of people seem to think they have to place nearly all their address in one field rather than use the next available feed down! There are also the customers with just plain large addresses.

 

[razzandy]

Hi Mick..... Thank U

That’s exactly the type of thing I need but it needs some tinkering to make it work for me. When I run it, it adds a space before the very first text found and it adds a space with every split it finds. Is there any way this can be fixed?

Now, here is where it gets mind boggling! I have Buyer Names in Column I, Address Line 1 in Column L, Address Line 2 in Column M and I have to insert a field in column N for Address Line 3. So maybe the best way to properly format all 4 fields would be to combine all 4 then split them across the four based on the cell limit of 22 characters each. I think this way is best because customers use any of the fields to try and fit their address in. If the total of all four cells proves to be to large to split correctly across the 4 cells then I’ll we can do is dump the remainder.

Hope this makes sense.

Thanks very much again for your solution and help its just great

 

[MickG]

Try adding a "Trim" to the 2 lines as below:-

If Len(Txt) >= 25 Then
            Dn.Offset(, c) = Trim(nTxt)
            Txt = Sp(n)
            nTxt = ""
            c = c + 1
        End If
        If Len(Txt) < 25 And n = UBound(Sp) Then
            Dn.Offset(, c) = Trim(Txt)
        End If

 

[razzandy]

Oh My God Mike…………. You are good.

How Can I limit the cell offset so it does not expand sideways more than 3 offsets, Total 4 cells wide (In the case of your example limited to A - D). I have other info in the 5 cell so I need it to end before offset of any more info and if there is more text just ignore/delete it.

Thanks again

I really appreciate your help

Cheers

Ryan