excel : retrieving numbers only from a cell 1

[bindi]

is there any way to retrieve a set of numbers only from a cell that contains both text and numbers
ie
the cell contains the following info

BIG W ROSNY PARK BLIGH STREET ROSNY TAS 7018

and i want ot grab just the postcode (ie the 4 digits at the end)

so i can create a corresponding column with just the postcodes in it

thanks
bindi

 

[DaleWatson123321]

Hi bindi,

If you want the post codes as "text", use:
=RIGHT(A2,4)

If you want to convert the text to a value, use:
=VALUE(RIGHT(A2,4))

Simply copy the formula down in an adjacent column for the number of rows of data.

If you'll be removing the "source" data (column), then of course you'll need to convert your post codes column to "values". To do this (after you've copied the formulas)...

a) Highlight the column
b) Copy the formulas
c) Use Edit - Paste Special - Values.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[nosugar]

Another way is to use the Text to Columns under the Data menu. If the addresses are about the same width try using a fixed width delineation.

Be sure to use this in a throw away work sheet and then cut and paste back into your live worksheet.

 

[PepeLeMokko]

To change your column to values, there is a much easier way.
Select the column and move it around with your right mouse button pressed. You may put it elsewhere or in the same place.
Anyhow, when you let the button go, a nice little menu will appear.
Choose " move here as values only" and bingo... it's OK