Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pull string before a certain character

I have a column with CA-, AWR-, THAMES-, etc. I want to pull the string
before the "-". It could be 2 to 6 characters long. Any ideas will be
appreciated. Thanks.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Pull string before a certain character

  1. Assuming your data is in column A, create a new column next to it (let's say column B) where you want to extract the characters.
  2. In cell B1, enter the following formula:
    Code:
    =LEFT(A1,FIND("-",A1)-1)
  3. Press Enter. This will extract the characters before the first "-" in cell A1.
  4. Copy the formula in cell B1 and paste it down to the rest of the cells in column B.

The LEFT function extracts a certain number of characters from the left side of a string. In this case, we're using the FIND function to locate the position of the first "-" in the string, and then subtracting 1 from that position to exclude the "-" itself from the extracted characters.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Pull string before a certain character

Please don't multi-post - you have an answer elsewhere.

Pete


On Feb 11, 1:43*pm, Sandra wrote:
I have a column with CA-, AWR-, THAMES-, etc. *I want to pull the string
before the "-". *It could be 2 to 6 characters long. *Any ideas will be
appreciated. *Thanks.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default Pull string before a certain character

The following formula will perform the extract you need:

=LEFT(A1,FIND("-",A1,1)-1)

Change cell A1 to match the cell you text value is in.
--
Kevin Backmann


"Sandra" wrote:

I have a column with CA-, AWR-, THAMES-, etc. I want to pull the string
before the "-". It could be 2 to 6 characters long. Any ideas will be
appreciated. Thanks.

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
Formula to pull first word from text string in a column CrisT Excel Worksheet Functions 15 January 25th 20 12:08 AM
Setting formulas to pull data from a specific character spacing... trice_nb Excel Worksheet Functions 2 October 15th 07 05:37 PM
pull numbers from text string [email protected] Excel Discussion (Misc queries) 7 March 19th 07 07:04 PM
Excel-Match 1st text character in a string to a known character? bushlite Excel Worksheet Functions 2 January 15th 07 07:36 PM
Pull last word from a text string in Excel nmp Excel Worksheet Functions 8 September 25th 06 10:13 PM


All times are GMT +1. The time now is 11:03 PM.

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"