Can this be done via formula (Sort) if not then VBA

[Sahil123]

Hi, i am trying to fit the message properly but for some reason it is formatting and pasting weirdly

Is there a formula way I could sort a street

so say I had:

1 Sandown

2 Sandown

3 Sandown

3 Blackheath

4 Blackheath

6 Sandown

8 Sandown

1 Blackheath

2 Blackheath

7 Sandown

5 Blackheath

6 Blackheath

19 Blackheath

4 Sandown

5 Sandown

Can I sort to something like this:

1 Blackheath

2 Blackheath

3 Blackheath

4 Blackheath

5 Blackheath

6 Blackheath

19 Blackheath

1 Sandown

2 Sandown

3 Sandown

4 Sandown

5 Sandown

6 Sandown

7 Sandown

8 Sandown

<tbody>
</tbody><colgroup><col></colgroup>

 

[Andrew Poulsom]

Is that data all in one column?

 

[Sahil123]

Is that data all in one column?

Hi yes they are in 1 column

 

[Andrew Poulsom]

You can put this formula in B1 and copy down:

=MID(A1,FIND(" ",A1)+1,255)&" "&TEXT(LEFT(A1,FIND(" ",A1)-1),"00")

Then sort A:B by column B.