Removing Line Breaks In A Cell

**Ross220681** · 01-25-2008, 05:41 AM

Hi,

I am trying to split cells that have addresses in them into separate cells via writing a formula but am having no joy, i wonder if anyone could help me please.

What i need is to split cell A1 which contains an address i.e

Red Roofs
12 Chiltern walk
Tunbridge Wells
Kent
TN1 1PL
England

I need to put a formula in cells B1,C1,D1,E1,F1 and G1 tha splits each line of the address and puts it in the separate cells so it would look like:

B1 Red Roofs
C1 12 Chiltern walk
D1 Tunbridge Wells
E1 Kent
F1 Tn1 1pl
G1 England

Hope there is a way to do this and thankyou very much for your help

Cheers

**dominicb** · 01-25-2008, 06:14 AM

Good morning Ross220681

...and welcome to the forum!!

I came up with this code as a solution to a recent post. Can you use this?
http://www.excelforum.com/showthread.php?t=629978

Originally Posted by DominicB

Not fully tested this but it seems to work OK.

Please Login or Register  to view this content.

It works for 4 line addresses, thus (say your address is in cell A1) :
=GetAddress1(A1,1) for line 1
=GetAddress1(A1,2) for line 2 etc up to line 4

You'll need to extend the code above to take account of 5 line addresses.

HTH

DominicB

**Ross220681** · 01-25-2008, 06:41 AM

Thats brilliant! i think that might be the way to go

Sorry for being completly stupid, this is to be written in VB? or is this a formula?

Thanks for your help

**dominicb** · 01-25-2008, 06:59 AM

Hi Ross220681

This is a custom function written in VBA. Pu the module into your workbook, and then use the formula as though it were a native function.

Open the workbook in Excel and press Alt + F11. This will open the VBE.
Select your workbook under the VBAProject window and go to Insert > Module and paste the code into the empty window that opens.
Now go to File > Close and Return to Microsoft Excel. This will close the VBE.

From within Excel you can now use these formulae in this workbook only :
=GetAddress1(A1,1) for line 1
=GetAddress1(A1,2) for line 2 etc up to line 4

HTH

DominicB

**Ross220681** · 01-25-2008, 07:20 AM

Tried that out and thats brilliant your an absolute life saver!

1 last question i promise, could you let me know what the code would be in VB if it was a 6 line address

Thanks again

**oldchippy** · 01-25-2008, 07:36 AM

Hi Ross,

This type of question comes up time and time again and dominicb has given you a solution to it that works. Could you please change the title of your question from Help!!!!!!!!!! to something more suitable to what you are actually asking.

http://www.excelforum.com/showthread.php?t=619135

**dominicb** · 01-25-2008, 07:40 AM

Hi Ross220681

Originally Posted by Ross220681

could you let me know what the code would be in VB if it was a 6 line address

Try this :

Please Login or Register  to view this content.

HTH

DominicB

**Ross220681** · 01-25-2008, 07:40 AM

No probs,

If someone could help finish off this code in VB that would be great

Many Thanks

**dominicb** · 01-25-2008, 01:34 PM

Hi Ross220681

Look at my last post.

HTH

DominicB

**dominicb** · 01-28-2008, 03:19 AM

Hi Ross220681

One more thing, make the second line of your function :

Please Login or Register  to view this content.

This will force an automatic recalculation of the function should the target cell change, the full modified function should read :

Please Login or Register  to view this content.

HTH

DominicB

**nickhibberd** · 03-10-2009, 11:14 AM

Awesome mate. That's exactly what I was loooking for

Cheers

Removing Line Breaks In A Cell

LinkBack

Thread Tools

Rate This Thread

Display

Removing Line Breaks In A Cell

Splitting Address in a cell

Re: Removing Line Breaks In A Cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions