Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default What formula do I use to copy only text on left side of a comma?

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,942
Default What formula do I use to copy only text on left side of a comma?

hi
from what you describe, i would suggest text to columns
2003 menubardatatext to columndelimited, commafinish
2007 data tabtool grouptext to columnsdelimited, commafinish

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

regards
FSt1

"Danielsjt" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default What formula do I use to copy only text on left side of a comma?

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.



Danielsjt wrote:

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


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default What formula do I use to copy only text on left side of a comma?

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))


Danielsjt wrote:

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


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default What formula do I use to copy only text on left side of a comma?

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.

"Danielsjt" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LEFT function-all to left of a comma? Jennifer F Excel Worksheet Functions 1 January 22nd 09 12:19 AM
how to make left side stay still and right side can move left to r AAS Excel Discussion (Misc queries) 1 May 27th 08 09:50 PM
change rows from left side to right side Rows on wrong side of worksheet[_2_] Excel Discussion (Misc queries) 1 July 27th 07 12:30 AM
Capitalizing text from the left limited by a comma Max Prophet Excel Discussion (Misc queries) 4 March 9th 07 10:11 PM
y-axis moves from the left hand side to the right hand side JP Excel Discussion (Misc queries) 1 March 13th 05 05:43 PM


All times are GMT +1. The time now is 02:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"