'Trim'....I think!

[EvansB2]

Hi there,

I'm having trouble trying to split out specific text in a longer text string. So, in the example below, Column B requires a formula to state either East, North, South or West, depending on what is entered in column A (as highlighted in red). I have a long list of data in column A and it is all uniform as can be seen that the text string starts with the same length of characters every time and so should make it possible to have the one formula.

Any assistance would be gratefully received as the data set is large!

Regards
Ben

	A	B
1	Location	Block Name
2	RainH TC | East B | 00 | 3005 | English Classroom	East
3	RainH TC | North B | 00 | 1062 | Mat Store	North
4	RainH TC | South B | 00 | 1001g | Corridor	South
5	RainH TC | West B | 00 | 1029 | SenCo	West
6
7

 

[AlanY]

try this

Book1
	A	B
1	Location	Block Name
2	RainH TC | East B | 00 | 3005 | English Classroom	East
3	RainH TC | North B | 00 | 1062 | Mat Store	North
4	RainH TC | South B | 00 | 1001g | Corridor	South
5	RainH TC | West B | 00 | 1029 | SenCo	West
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=LOOKUP(2,1/ISNUMBER(SEARCH({"East","South","North","West"},A2)),{"East","South","North","West"})

 

[FormR]

Hi, here's another option you can also try.

Book1
	A	B
1	Location	Block Name
2	RainH TC | East B | 00 | 3005 | English Classroom	East
3	RainH TC | North B | 00 | 1062 | Mat Store	North
4	RainH TC | South B | 00 | 1001g | Corridor	South
5	RainH TC | West B | 00 | 1029 | SenCo	West
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",A2)+2,255)," ",REPT(" ",255)),255))

 

[EvansB2]

Thank you very much both of you. Have successfully tested both approaches.

I'll stick with the 'TRIM' as it will be more robust when potential spelling anomalies creep in over time!

Ben

 

[AlanY]

Thank you very much both of you. Have successfully tested both approaches.

I'll stick with the 'TRIM' as it will be more robust when potential spelling anomalies creep in over time!

Ben

you're welcome
glad that work out for you