formula for post codes

[brianaclark1967]

Hi all,

Is there a formula that will pull in just the first part of a post doe, for example all letters and numbers before the space in the middle.

I've attached a quick sheet so you can see what I want the result to be.

Looking forward to solving this, as it's been driving me crazy trying to solve it.

Regards,

Brian

 

[Chihiro]

Something like below. In D3 or C3 and copy down.

=LEFT(B3,SEARCH(" ",B3)-1)

 

[David Evans]

Try this

Having been born and raised in the UK, I know that the Postcode is made up of The Outward Code (the bit before the space) and the Inward Code (the three characters after the space). My formula removes the final three characters and the space, by extracting the total string length and removing the Inward code and the space.

As long as the PostCode arrives in its complete form, it should work fine - very occasionally in the UK, people just use the first 4 characters and that would cause the formula to produce erroneous results. The other methods would also produce an error unless there was a trailing "space" after the code. If this is a possible outcome for your data, let us know - there's an easy fix for it, but why complicate matters unless you need it?!

DME
originally from SA48 7EE
exported to 92651

 

[Villalobos]

Or this: =LEFT(B3,FIND(" ",B3&" ")-1)

 

[Khalid NGO]

Hi Brian,

Another option with Replace and Find (formula) :
=REPLACE(B3,FIND(" ",B3),99,"")


Non-Formula (using Find & Replace method) :

• Select your range
  
• Copy/paste special > values
• Press Ctrl+H (Find & Replace)
• Find what: a space and *
• Leave blank Replace with field
• Replace all

Regards,

 

[Vishal Srivastava]

One more:

=TRIM(LEFT(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),LEN(B3)))

Thanks & Regards,
CMA Vishal Srivastava

 

[bosco_yip]

And,

=SUBSTITUTE(B3,RIGHT(B3,4),)

Regards
Bosco

 

[brianaclark1967]

One more:

=TRIM(LEFT(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),LEN(B3)))

Thanks & Regards,
CMA Vishal Srivastava

Thank you Vishal,

Really appreciate your help.

Regards

Brian

Hi Brian,

Another option with Replace and Find (formula) :
=REPLACE(B3,FIND(" ",B3),99,"")


Non-Formula (using Find & Replace method) :

• Select your range
  
• Copy/paste special > values
• Press Ctrl+H (Find & Replace)
• Find what: a space and *
• Leave blank Replace with field
• Replace all

Regards,

Hi Brian,

Another option with Replace and Find (formula) :
=REPLACE(B3,FIND(" ",B3),99,"")


Non-Formula (using Find & Replace method) :

• Select your range
  
• Copy/paste special > values
• Press Ctrl+H (Find & Replace)
• Find what: a space and *
• Leave blank Replace with field
• Replace all

Regards,

Or this: =LEFT(B3,FIND(" ",B3&" ")-1)

Try this

Having been born and raised in the UK, I know that the Postcode is made up of The Outward Code (the bit before the space) and the Inward Code (the three characters after the space). My formula removes the final three characters and the space, by extracting the total string length and removing the Inward code and the space.

As long as the PostCode arrives in its complete form, it should work fine - very occasionally in the UK, people just use the first 4 characters and that would cause the formula to produce erroneous results. The other methods would also produce an error unless there was a trailing "space" after the code. If this is a possible outcome for your data, let us know - there's an easy fix for it, but why complicate matters unless you need it?!

DME
originally from SA48 7EE
exported to 92651

Thank you guys,

That was all really helpful.

I really appreciate the assistance

Regards

Brian