have 3 pieces of data in a cell seperated by a comma. How can I isolate the middle piece of data?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've imported a large file of data that is basically three columns of data each seperated by a comma. (I could not figure out how to import them into a new spreadsheet so that each piece of data was automatically put into it's own column - but that is a different question)

How can I do a "search replace" that will delete all of the data up to the firts comma?

Then I would want to delete the data after the second comma in order to isolate the middle portion of data?

Here is an example of the data: "10302003-12445","17.99","xyz21"

Is there a better way to accomplish this?
 
Hi dcb,

When opening the workbook, you can specify the comma delimiter as follows:

Workbooks.Open Filename:="test.txt", Format:=2

If you want to process the strings after the workbook is opened, you can use
Text to Columns (Data | Text to Columns) or loop through the cells and parse
them with the Split() function:

MsgBox Split(Cells(1,1).Value, ",")(1)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
dcb,

Make sure you have 2 empty columns to the right of your
imported data. Select the column containing your imported
data and from the menus select Data -> Text to Columns.
This will pop up a wizard, in step 1 choose delimited and
hit next. In step 2 choose select Comma as the delimiter
and hit finish. You probably don't need step 3 (sets the
format of each column).

Dan E

dcb said:
I've imported a large file of data that is basically three columns of data each seperated by a comma. (I could not figure out how
Click to expand...
to import them into a new spreadsheet so that each piece of data was automatically put into it's own column - but that is a
different question)
 
I think the quickest way is to select the column (the data is in one spreadsheet column only, right?),
then do Data>Text to columns with comma as separator.

HTH
Anders Silvén
 
Oh, and for a non-programming solution, you should be able to simply open
the file and use the text import wizard, specifying a comma delimiter.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top