Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default trim everything from left of first underscore in the text?

Hi
I am trying to remove all characters to the left of the first occurence of an
underscore but have not found a good solution. do you know how to do this?
thanks in advance - M

I have test in columns like this:

date_123232_5.txt
date234._2343453_356.txt

I want it to look like this:

123232_5.txt
2343453_356.txt

these will later be transformed to hyperlinks to the exact file.

--
Misty :-)

Message posted via http://www.officekb.com

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default trim everything from left of first underscore in the text?

Hi,

With your text in a1 try this and drag down

=MID(A1,FIND("_",A1)+1,LEN(A1))

Mike

"Mitchell_Collen via OfficeKB.com" wrote:

Hi
I am trying to remove all characters to the left of the first occurence of an
underscore but have not found a good solution. do you know how to do this?
thanks in advance - M

I have test in columns like this:

date_123232_5.txt
date234._2343453_356.txt

I want it to look like this:

123232_5.txt
2343453_356.txt

these will later be transformed to hyperlinks to the exact file.

--
Misty :-)

Message posted via http://www.officekb.com


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default trim everything from left of first underscore in the text?

Try this:

=RIGHT(A1,LEN(A1)-SEARCH("_",A1))

Hope this helps.

Pete


On Jul 28, 7:24*pm, "Mitchell_Collen via OfficeKB.com" <u33726@uwe
wrote:
Hi
I am trying to remove all characters to the left of the first occurence of an
underscore but have not found a good solution. do you know how to do this?
thanks in advance - M

I have test in columns like this:

date_123232_5.txt
date234._2343453_356.txt

I want it to look like this:

123232_5.txt
2343453_356.txt

these will later be transformed to hyperlinks to the exact file.

--
*Misty :-)

Message posted viahttp://www.officekb.com


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default trim everything from left of first underscore in the text?

You could use a helper cell with a formula:

=MID(A1,SEARCH("_",A1,1)+1,255)

The 255 is just a big number that is large enough to grab all the remaining
text.

"Mitchell_Collen via OfficeKB.com" wrote:

Hi
I am trying to remove all characters to the left of the first occurence of an
underscore but have not found a good solution. do you know how to do this?
thanks in advance - M

I have test in columns like this:

date_123232_5.txt
date234._2343453_356.txt

I want it to look like this:

123232_5.txt
2343453_356.txt

these will later be transformed to hyperlinks to the exact file.

--
Misty :-)

Message posted via http://www.officekb.com


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default trim everything from left of first underscore in the text?

Thanks so very much! Your solution did the job!

-M

--
Misty :-)

Message posted via http://www.officekb.com

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
underscore in email address covered by hyperlink underscore Chuck Bowser Excel Discussion (Misc queries) 1 April 22nd 09 05:47 PM
TRIM, LEFT, RIGHT cware Excel Worksheet Functions 3 March 3rd 09 11:38 PM
left trim Finger Tips Excel Worksheet Functions 9 April 29th 07 02:40 AM
Splitting 1 cell into 2 based on underscore in text RJF Excel Worksheet Functions 5 May 9th 06 07:50 PM
text trim combine Ian Bartlett Excel Discussion (Misc queries) 4 December 14th 05 01:37 AM


All times are GMT +1. The time now is 11:19 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"