Excel 2007

[alter_ego1981]

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

 

[slurpee55]

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).