What formula do I use to copy only text on left side of a comma?

  • Thread starter Thread starter Danielsjt
  • Start date Start date
D

Danielsjt

I have a spreadsheet and in column D contains Equipment #'s and their
description separated by a comma. How can I separate the Equipment #'s into
Column G.
Sheet contains approx. 1500 rows.

Example:
T-8900-F, Fresh Water storage Tank
 
hi
from what you describe, i would suggest text to columns
2003 menubar>data>text to column>delimited, comma>finish
2007 data tab>tool group>text to columns>delimited, comma>finish

make a back up prior to runing the text to column wizard.

regards
FSt1
 
If there's exactly one comma in each of those cells, you can use this technique:

Insert a new column E
Select the range in column D
Data|text to columns (in xl2003 menus)
delimited
by comma
and you'll be done

But don't use this if your data can have multiple commas in any of the cells.
You'll end up with more than 2 columns of data if you do.
 
ps. If you really want to have the stuff on the left side of the comma, you can
use:

=trim(left(d1,search(",",d1&",")-1))
 
If it is just the right side of the comma you want, then try
=TRIM(RIGHT(D2,LEN(D2)-FIND(",",D2)))
As Dave said, this works based on the position of the 1st comma and he's
given you the formula for the left half if that's what you want.
 
Back
Top