Moving Numbers from a Cell into another Cell

[bluearmy35]

I was hoping someone can solve this problem for me, i have an address in cell C3 that says 123 Pembroke Lane, now i want to move the 123 from C3 to C2 and have this all the way down the spreadsheet (formula) so that all other numbers are stripped from the address???

Any easy way of doing this????

Cheers

 

[sawedoff]

If the addresses always have the numbers first, then a space, then the street, and what you want to split out is always the first numbers, then use this formula:
=LEFT(C3,FIND(" ",C3)-1) to put the numbers into a blank column B,
and this formula:
=RIGHT(C3,LEN(C3)-FIND(" ",C3)) to put the remainder of the address ito a blank column (I used D). Copy the formulas down, then after you get the whole column spit, use Copy-Paste Special-Values to eliminate the formulas in B and D, then delete column C.

Sawedoff

 

[emma113]

I have an Excel file with two worksheets. I would like to write a VBA macro that will search cell B in worksheet 1, if it finds an X (in cell B) cut the line (row) and paste this line at the end of the second worksheet. This should be done for all rows in worksheet 1 (looping) until end of the worksheet1.

Thanks

 

[sawedoff]

Emma113 - I think you probably meant to post your question in a separate post, Try again and you're more likely to find an answer.

Sawedoff