Removing characters from the left and right of commas

kos666

New Member
Joined
Aug 10, 2010
Messages
6
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! :eek:

Any comments, ideas or help is greatly appreciated.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
Also,
Excel Workbook
AB
1STREET1,TOWN1,NUMBER1TOWN1
2STREET2,TOWN2,NUMBER2TOWN2
3STREET3,TOWN3,NUMBER3TOWN3
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B1=MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board!

Yet another way:
Excel Workbook
LMN
1AddressCity
2STREET1,TOWN1,NUMBER1TOWN1
3STREET2,TOWN2,NUMBER2TOWN2
4STREET3,TOWN3,NUMBER3TOWN3
Sheet1
Excel 2007
Cell Formulas
RangeFormula
N2=TRIM(MID(SUBSTITUTE(L2,",",REPT(" ",255)),255,255))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top