Software

Ask something
Discussion Locked

Excel or Access problem

0 helpful
7 replies

by jimmy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Excel
Hi, I have this probelm in Excel I am trying to deal with, problem is this:

Spreadsheet 1 has customer address Field names in single fashion so it looks like this
cell A1 address 1 - 30
cell B1 address 2 - streetname
cell C1 address 3 - postcode

spreadsheet 2 simply has:
cell A1 address - 30 streetname postcode

Is there any way i can get all the data from sheet1 to sheet2. so it will convert 30 trail street edinburgh from spreadsheet 1 into the three different categories in spreadsheet 2. e.g.

address 1 = 30
address 2 = trial street
address 3 =edinburgh

7 replies

by dklippert

on worksheet 2, hightlight the addresses.
Go to Data>Text to Columns.
Select the Delimited option, click Next.
Put a check in the Space box. Click Finish.
Make sure there is no data to the right of the addresses.
Now you can copy/cut paste to Worksheet 1
If you want to go the other direction:
In worksheet 2 us the formula:

=Worksheet1!A1&" "&Worksheet1!B1&" "&Worksheet1!C1

by thamer

... from what I understand, I think you want have the text in sheet2 which has "30 trail street edinburgh" to be cut/seperated into the the address format in sheet1.

If that so,... then here are your formulas.

Let's assume sheet2 have the following addresses.
Sheet2
A1:30 trial street edinburgh
A2:356 fox street sesemee
A3:123 north street london
A4:30 11th street luxmborg

Then,..In sheet1 cell A1 type the following:
A1:=MID(Sheet2!A1,1,FIND(" ",Sheet2!A1,1))
It will return: 30

in sheet1 cell C1 (start with this before B1), type the following:
C1: =MID(Sheet2!A1,FIND("street",Sheet2!A1,1)+7,LEN(Sheet2!A1))
It will return: edinburgh

in sheet1 cell B1, type the following:
B1: =MID(SUBSTITUTE(Sheet2!A1,D1," "),FIND(" ",Sheet2!A1,1)+1,LEN(SUBSTITUTE(Sheet2!A1,D1," ")))
It will return: trial street

Now, in sheet1, select the cells from A1:C1 and copy down to A2:C2, A3:C3 and A4:C4.

You will have the following under columns A,B and C:
A*******B**************C
A1:30***trial street***edinburgh
A2:356**fox street*****sesemee
A3:123**north street***london
A4:30***11th street****luxmborg

Note:
-For the formulas to work right, the word "street" MUST be available in the addressses under sheet2 as i used it as the parameter to distinguish between Address 2 and address 3 in sheet1.
- Since you did not specify if all the addresses in sheet2 starts with "30", .... address1 formula in sheet1 will take the values until it encounters a space returning what ever ... is there.

Take care.

by thamer

No wonder ....
Please supstitute the two occurance of D1 by C1 in the formula for Cell B1.

The formula under Sheet1 Cell B1 should be as follows.

=MID(SUBSTITUTE(Sheet2!A1,C1," "),FIND(" ",Sheet2!A1,1)+1,LEN(SUBSTITUTE(Sheet2!A1,C1," ")))
The workbook is on its way.

Sorry for late respond.

by jimmy

Hi Tharner,

Thanks for the reply, unfortunately i can't get your spreadsheet to work.
If you have created it in Excel could you post it to me at yteebison@hotmail.com
Very much appreciate taking the time to reply regardless.

This discussion is closed to new replies.