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 find a character location in a string starting from the right

I have to find the second instance from the right in strings with a variable
number of segments delimited by ".", where the final string is reliably 6
characters and the remaining segments are of variable length.

Example string:
mypage.My_Details.Yorkshire_and_Humber_region_page .Accountants_shown_ways_of_going_for_Olympic_gold_ .136277

From which I want to extract:
Accountants_shown_ways_of_going_for_Olympic_gold_. 136277
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default find a character location in a string starting from the right

=MID(A1,FIND("~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,255)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"funtipoom" wrote in message
...
I have to find the second instance from the right in strings with a
variable
number of segments delimited by ".", where the final string is reliably 6
characters and the remaining segments are of variable length.

Example string:
mypage.My_Details.Yorkshire_and_Humber_region_page .Accountants_shown_ways_of_going_for_Olympic_gold_ .136277

From which I want to extract:
Accountants_shown_ways_of_going_for_Olympic_gold_. 136277



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 287
Default find a character location in a string starting from the right

Another way....

=REPLACE(A1,1,LOOKUP(LEN(A1)-7,FIND(".",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")


"Bob Phillips" wrote:

=MID(A1,FIND("~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,255)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"funtipoom" wrote in message
...
I have to find the second instance from the right in strings with a
variable
number of segments delimited by ".", where the final string is reliably 6
characters and the remaining segments are of variable length.

Example string:
mypage.My_Details.Yorkshire_and_Humber_region_page .Accountants_shown_ways_of_going_for_Olympic_gold_ .136277

From which I want to extract:
Accountants_shown_ways_of_going_for_Olympic_gold_. 136277




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
Excel-Match 1st text character in a string to a known character? bushlite Excel Worksheet Functions 2 January 15th 07 07:36 PM
Starting at cell with string in VBA [email protected] Excel Discussion (Misc queries) 2 September 21st 06 03:54 PM
sort starting from a specific character (@ sign) for email addres RobJ Excel Discussion (Misc queries) 1 June 12th 06 09:18 PM
Find last occurance of character in text string JDay01 Excel Worksheet Functions 2 February 14th 06 05:29 PM
Find nth instance of a character in a string Francis Hayes (The Excel Addict) Excel Discussion (Misc queries) 7 January 21st 05 04:44 PM


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