Address in one cell

S

SS

Hi

I have a spreadsheet where the whole of address appears in one cell. Is
there anyway I can change this so that each line of the address goes into
separate cells across the row?

Thanks
Shona
 
S

SS

Thanks for that but it doesn't do what I want I want each line of the
address in a separate column.

I get the same result if I unwrap the cell.

Any other ideas would be great?

Cheers Shona
 
M

mk

Thanks for that but it doesn't do what I want I want each line of the
address in a separate column.

I get the same result if I unwrap the cell.

Any other ideas would be great?
Click to expand...

Hi, can you give two-three examples of your address?

Marcin
 
S

SS

In A1
Bank House
Dollar
Clacks

In A2
The Lodge
End Lane
Swindon
Wiltshire
SW9 8BK

There is no blank row between the addresses I've just put that here to break
it up for you

Hope that helps

Thanks Shona
 
M

Max

FWIW, the method outlined below would work, but only if
the number of address lines per cell are the *same*
(.. from your posted samples, it unfortunately doesn't seem to be case ..)

If you have:

In A1:
AddressALine1
AddressALine2
AddressALine3

In A2:
AddressBLine1
AddressBLine2
AddressBLine3

Select A1:A2 > Copy

Open Word
----------
In Word
----------

Click Edit > Paste special > Unformatted text > OK

Press Ctrl + A
(this selects all the pasted text)

Click Edit > Replace > Replace tab

Enter in the box for Find what: "
[i.e. enter a single double quote]

Leave the box for "Replace with:" blank

Click 'Replace All'

Answer 'No' to exit the prompt to search the remainder of the doc

Click 'Close' to exit the Find and Replace dialog

Right-click on the entire selection of text > Copy

---------
Toggle back to Excel
----------

In a new sheet, right-click on A1

Choose Paste special > Text > OK

This will paste the address lines into 6 *separate* cells in col A

AddressALine1
AddressALine2
AddressALine3
AddressBLine1
AddressBLine2
AddressBLine3

Put in B1: =OFFSET($A$1,ROW()*3-3+COLUMN()-2,)
Copy B1 across to D1, then copy down until zeros appear

This will re-arrange the data in col A into 3 separate columns:

AddressALine1 AddressALine2 AddressALine3
AddressBLine1 AddressBLine2 AddressBLine3

The OFFSET can be easily adapted to suit the number of Address lines

If it is 4 lines per address instead of 3, just change the

"ROW()*3-3" part to "ROW()*4-4"

and copy across 4 columns (B1 to E1), instead of 3

And so on.
 
B

b&s

recent advice from Frank Kabel:
......
if your data is in column A enter the following in column B
=SUBSTITUTE(A1,CHAR(10),"#")
- copy down for all rows
- select column B and copy it (CTRL+C)
- goto 'Edit - Paste Special' and choose 'Values'
- after this select column B and goto 'Data - Text to Columns'. choose
in this case the '#' as delimiter and finish the wizard
.....

--
regards/pozdrav!
Berislav

- Always backup your data before trying something new -
*****************************************************************
ROT13 - email address [email protected]
 
Top