Excel 2007, splitting text

[Woden]

This should be really simple but I am just blacking out. I need to go down a list of postcodes and pull just the first part. The best method I could come up with, doesnt actually exist as a thing

Cells(rowNumber, 3) = Cells(rowNumber, 1).Split(0)

 

[andshrew]

What format are your post codes in?

If they all contain a space then you can simply use the built in Text to Columns function to split it.

Or a function like =LEFT(A1,SEARCH(" ",A1,1)-1) assuming A1 contains your postcode.

If they are fixed length then you should be able to adjust the above to take the first 4 characters then remove the trailing spaces afterwards.

 

[Woden]

They are always split traditionally, so like AB1 2CD (only valid, obviously).
Dumping the "AB1" into a cell is just a placeholder for the next step, I will then need to use that first bit to match up with something else int he function. That's why I am using VBA.

The function I am trying to write will eventually dump a location into the cell ready for me to do other stuff with it.

This would be so much easier if IT just let me have Python lol.

 

[ArchAnGeL]

Cells(rowNumber, 3) = application.left(Cells(rowNumber, 1))

or something similar . not got excel on this pc

 

[gillywibble]

Use Text to Columns and delimit by space.

If some of the postcodes don't have spaces, use the following formula first:-

=CONCATENATE(LEFT(A1,LEN(A1)-3)," ",RIGHT(A1,3))

 

[wonko]

It might be an idea to use TRIM() first to make sure there are no leading or trailing spaces. The first part of a postcode can be between 2 and 4 characters IIRC.

Edit: Found this which might be useful as you're already using VBA
http://www.mrexcel.com/forum/excel-questions/475161-uk-post-code-re-format.html

 

[cotton31]

Or just =LEFT(A1,LEN(A1)-3) to get the first part of the post code (change to -4 if the post codes contain a space)