Text To Columns Not Working - Formula?

[SimonHughes]

Hello, I am a bit puzzled here as the text to columns is not working - there must be something in the formatting that is causing a problem. I am trying to get rid of the postcode after the city name and text to columns seemed the best way to do it. The column is formatted as "General" and I am using MS 365.

A formula would work as well so any suggestions on that score will be appreciated too.

Peterhead AB42

Aviemore PH22

Edinburgh EH4

Glasgow G2

Prestwick KA9

Newcastle NE13

Newcastle NE3

Sunderland SR6

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	Peterhead AB42	Peterhead
3	Aviemore PH22	Aviemore
4	Edinburgh EH4	Edinburgh
5	Glasgow G2	Glasgow
6	Prestwick KA9	Prestwick
7	Newcastle NE13	Newcastle
8	Newcastle NE3	Newcastle
9	Sunderland SR6	Sunderland
Sheet7

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTBEFORE(A2," ",-1)

 

[SimonHughes]

How about

Fluff.xlsm
	A	B
1
2	Peterhead AB42	Peterhead
3	Aviemore PH22	Aviemore
4	Edinburgh EH4	Edinburgh
5	Glasgow G2	Glasgow
6	Prestwick KA9	Prestwick
7	Newcastle NE13	Newcastle
8	Newcastle NE3	Newcastle
9	Sunderland SR6	Sunderland
Sheet7

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTBEFORE(A2," ",-1)

It's returning an #N/A

 

[Fluff]

Sounds like the spaces are not normal spaces, try

=TEXTBEFORE(A2,CHAR(160),-1)

 

[SimonHughes]

Sounds like the spaces are not normal spaces, try

=TEXTBEFORE(A2,CHAR(160),-1)

Hi Fluff, that fixed it, many thanks indeed!

 

[Fluff]

You're welcome & thanks for the feedback.