Excel 2007

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

alter_ego1981

Thread Starter
Joined
Jun 26, 2008
Messages
49
Hi All,

Hope you can help, I have a slight problem here which my boss thinks can be sorted with a clever macro but I am not to sure.

Basically we have a spreadsheet full of data which are are having to maniplute by hand in the address fields. some of the data lines are ok as they are but others we have to copy and paste into another cell,

i have attached an example, as you can see its a mixture of business name and number in one cell and the rest of the address in another.

Hope you understand and can help.

Thanks
 

Attachments

Joined
Oct 20, 2004
Messages
7,837
So you have a list of all possible business names and/or addresses? Given the randomness of the data you uploaded (one business name is 0 words long, another 1 word and another 2 words) it would be hard to break it out properly.
However, these two formulas work on the data as listed - the only item you might have to adjust would be the number of letters subtracted (the "-3" and the "3" in the equations).
For the name:
=IF(ISERROR(LEFT(A2,LEN(A2)-3)),"",TRIM(LEFT(A2,LEN(A2)-3)))
For the address:
=TRIM(RIGHT(A2,3)&" "&B2).
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top