Split data in cells

[Neil]

Hi


I have in a cell A1 an address e:g 103, North Road, Qw12 5rw

I know you can use the Concatenate function to merge things togethe in a
cell but can i do it the opposite way so i end up with the 3 in differnet
cells such as A2 103 A3 North Road, A4 Qw12 5rw ?

Many thanks for looking

 

[Mike H]

Neil,

Select your column then
Data,Text to columns
select delimited
next
check Comma
Finish

Mike

 

[David Biddulph]

Data/ Text to Columns/ Delimited:
Specify comma as delimiter.

That would put the data in A1:C1, so you may want to do some shuffling if
you want to get it into rows rather than columns, and you'd have to copy the
data before you start if you want to keep a copy of the original in A1.

Another option would be combinations of LEFT, MID, and RIGHT, using FIND to
identify your comma delimiters to determine the break points.

 

[FSt1]

hi,
2 ways.
if your address is seperated with commas you might try the text to columns
function
on the menu bar...data>text to columns.
another. lookup the left, mid, and right formula functions in xl help.
the formulas would work on your example addres like this...
=LEFT(A1,3)=103
=MID(A1,6,10)= North Road
=RIGHT(A1,8) = Qw12 5rw
draw back of this method...if you have a lot of address of varying length,
you would have to keep adjusting the formulas to fit each address.

regards
FSt1

 

[Cimjet]

Hi Neil
The sample your showing is separated by "commas" if they are all like that
Go to Data-Text to Columns and follow the menu. Im using XL2003.
Good luck
Cimjet

 

[Gord Dibben]

Neil

Use the Data>Text to columns as others have suggested.

Info only and a warning.............most applications that would use your data
from Excel would prefer the address bits are in their own column across one row
per address.

Putting them down one column per your request will give you manipulation
problems down the road when sorting or filtering in Excel.


Gord Dibben MS Excel MVP