Removing characters from the left and right of commas

[kos666]

Hello there,

I have a CSV text file that is created by a script, the said script then opens up a spreadsheet which imports the CSV file, filters, tidies up the data & adds a header line in Row A.

What I require is the data in Column L, which looks like this:-

STREET1,TOWN1,NUMBER1
STREET2,TOWN2,NUMBER2
STREET3,TOWN3,NUMBER3

To be redisplayed in Column N in this format:-

TOWN1
TOWN2
TOWN3

I've come across some code that will take out characters from the right of a comma but haven't been manipulate it for what I require.

I did think about using Concatenate & breaking up the line as I imported the CSV but I do have a lot of columns already & code that is Column dependant. So I was hoping for an easier solution before having to rewrite everything!

Any comments, ideas or help is greatly appreciated.

 

[SteveO59L]

Try Data, Text to Columns with the "," as the delimiter

 

[Sandeep Warrier]

Also,

Excel Workbook
	A	B
1	STREET1,TOWN1,NUMBER1	TOWN1
2	STREET2,TOWN2,NUMBER2	TOWN2
3	STREET3,TOWN3,NUMBER3	TOWN3
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1)

 

[Jon von der Heyden]

Welcome to the board!

Yet another way:

Excel Workbook
	L	M	N
1	Address		City
2	STREET1,TOWN1,NUMBER1		TOWN1
3	STREET2,TOWN2,NUMBER2		TOWN2
4	STREET3,TOWN3,NUMBER3		TOWN3
Sheet1

Excel 2007

Cell Formulas
Range	Formula
N2	=TRIM(MID(SUBSTITUTE(L2,",",REPT(" ",255)),255,255))