Numbers Help Needed?

[Futhark]

Hi all, i've ran into a problem that might not be easily fixed but i hope someone might be able to prove me wrong I have a table with 51,736 rows. The issue here is my column 1 is a list of Postcodes for a region which was fine but now i need the postcodes broken down into 3 columns so instead of the column for example having BT42 1AA in it i need it broken down into 3 like BT42 then 1 then AA. If someone knows anyway this could be done easily it would be much appreciated.

Many Thanks.

 

[Ap0ks]

Are all the rows in the same format or do they differ?

To get the first part (assuming there is always a space) you'll want something like =LEFT(A1,FIND(A1," ")) basically find the space and return all the characters to the left of it

The second part (assuming it's always 3 characters from the end) you need something like =LEFT(RIGHT(A1,3),1) which is to take the last 3 characters from the right then the first character from the left of that.

Then finally (again assuming 3 characters at the end) you'll want =RIGHT(A1,2) to give the two characters from the right-hand side.

Put those three formulas (they may very well need tweaking) into cells B1, C1 and D1 and then copy them down the columns. Finally you can copy and paste the values from columns B, C & D into a new sheet to preserve the values.

 

[Futhark]

Are all the rows in the same format or do they differ?

To get the first part (assuming there is always a space) you'll want something like =LEFT(A1,FIND(A1," ")) basically find the space and return all the characters to the left of it

The second part (assuming it's always 3 characters from the end) you need something like =LEFT(RIGHT(A1,3),1) which is to take the last 3 characters from the right then the first character from the left of that.

Then finally (again assuming 3 characters at the end) you'll want =RIGHT(A1,2) to give the two characters from the right-hand side.

Put those three formulas (they may very well need tweaking) into cells B1, C1 and D1 and then copy them down the columns. Finally you can copy and paste the values from columns B, C & D into a new sheet to preserve the values.

Thanks so much for these formulas, I will get testing them in the morning and fingers crossed i'll get this sorted. The Postcode is spaced like this BT42 2AA i need the first four to stay connected BT42 then the remaining 2AA needs split like this 2 & AA i hope this makes sense

After a little work i got the formulas to work, THANK YOU !!!! So now i have my A1 Postcode Column split into 3 as needed, these are now B1, C1 & D1. If i delete A1 this will obviously mess up my formula but i don't want this Column now, I can hide the column but if i export the table will it export the hidden column too? or is there a way to remove the formulas in the cells but keep the results we have?

 

[Futhark]

Problem Solved, Many Thanks