Moving the Number in an address

[sellnow]

I have an street address in a cell B (1234 main street). would like to extract the number only (1234) and move that number to cell A. This repeats 1000 times how can I do this easily, so not cutting and pasting a 1000 times?
Thanks

 

[devonknows]

Hi,

First you need to locate a common indentifier, for an exmaple i will use a space 1234(space)main(space)street. So we look for the first space within the text:

=FIND(" ", B1) 'This will return 5

However we only want 4 becuase its located the number where the space is and we just want the number so to put it all together we want to select the first part of the string so the function is LEFT as here:

=LEFT(B1, FIND(" ", B1)-1)

We minus one becuase if we used 5 you would have "1234(space)"

Hope this helps,
Regards,

 

[sellnow]

Devon that worked great....the number moved to cell A...great.....but its leaving the number still in cell B, I need to cut it, not copy the number. I want to be able to sort off the street name, with the number removed.....I hope this makes sense.
Thanks for the first part it worked great, sorry I was not clear.

 

[devonknows]

Hi,

That cant be done by means of a formula, you would have to go into the realms of VBA. The procedure can be relatively simple but if your not familiar with VBA then it will be fairly difficult and its not something i can teach over this.

My apologies.

Regards,

 

[devonknows]

Ignore that comment i may have found a way, wait there!

 

[devonknows]

Ok you cant replace the text but what you can do is insert another column so it would look like this:

House Num        Street Name     All Together
1234             Main Street     1234 Main Street

Column a would remain the Same and just insert a column between A & B.

Column B would then be

=RIGHT(C3,LEN(C3)-FIND(" ",C3))

Sorry , but there isnt a formula that can repalce the text in another cell, only its own.

Hope this will help.
Regards,

 

[sellnow]

Devon, Awesome...two step process is still much better then cut and paste 1000 times. Worked perfect.
Thanks

 

[devonknows]

Good Stuff,

if you right click on Column C Head and click "Hide" the column will hide but the formulas will still work becuase you cant delete that data otherwise the formula's will return #VALUE

Or Copy and Right Click to a new Sheet "Paste Special" and select Values for columns A& B