0

I need to split an address into rows for postage. In Tab 1 I have my members details In Tab 2 I have my Invoice that we mail out.

In Tab 2 I run a VLOOKUP function to pull the member's details from Tab 1

PO Box 211, Alberton, 1450

Now the problem starts...

Normal "Text to Columns" can't be used due to the formula bar showing the VLOOKUP formula instead of the actual address.

=IF(F7=""," ",VLOOKUP(F7,'Permanente en Jaarlede'!B4:Y2063,24,FALSE))

How do I change my address from:

PO Box 211, Alberton, 1450 to:

PO Box 211
Alberton
1450

when the text is the result of a formula, not just a bare value?

Excellll
  • 12,847
Jacques Smit
  • 1

2 Answers2

0

I don't know how to do it with Excel Formulae but this VBa will do it.

First thing, is take a back up of your file before running it.

It will take the value of A1 and split it over multiple lines

Public Function Addy(cellToLookup As String)

Dim result As String
result = Range(cellToLookup).Value

Dim splitty() As String
splitty = Split(result, ",")

Dim i As Integer
i = 0

For i = 0 To UBound(splitty)

Range("B" & i + 1).Value = splitty(i)   'update for start row. EG, change B to col, and the +1 to +15 (if you want it to start on row 15)

Next i

End Function

How do I add VBA in MS Office?

What isn't clear form your question is, how many rows you will need and if you need to perform this multiple times etc but, this hopefully can get you going. You may need to perform the look up with VBa too, which should be easy enough.

Community
  • 1
Dave
  • 25,513
0

You could substitute the ", " with "CHAR(10)".

Make sure you have line wrapping (Wrap Text) enabled for the cell with multiple rows:

If A1 looked like PO Box 211, Alberton, 1450, in your new cell use:

=SUBSTITUTE(A1,", ",CHAR(10)).

The new cell would look like: 

PO Box 211
Alberton
1450

reference: substitute-a-comma-with-a-break-link-in-a-cell

Community
  • 1
Lars
  • 9