A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Find second to last value in cell



 
 
Thread Tools Display Modes
  #1  
Old April 25th, 2010, 01:44 PM posted to microsoft.public.excel.worksheet.functions
Eric_G
external usenet poster
 
Posts: 34
Default Find second to last value in cell

Unless most "right" or "left" functions in which one is looking to select the
"LAST" or the "FIRST" value within a string of characters within a call, I
wish to select the "SECOND TO LAST" value within a string of characters
within a call. IN other words, assume the string of characters of a cell
ends with "Fname Lname". I wish to select "Fname" from the character string
and place it in another column. Any ideas? Much appreciated.
  #2  
Old April 25th, 2010, 02:05 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default Find second to last value in cell

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99))


"Eric_G" wrote:

Unless most "right" or "left" functions in which one is looking to select the
"LAST" or the "FIRST" value within a string of characters within a call, I
wish to select the "SECOND TO LAST" value within a string of characters
within a call. IN other words, assume the string of characters of a cell
ends with "Fname Lname". I wish to select "Fname" from the character string
and place it in another column. Any ideas? Much appreciated.

  #3  
Old April 25th, 2010, 05:04 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Find second to last value in cell

Very nice solution, Teethless Mama!
--
Regards
Roger Govier

Teethless mama wrote:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99))


"Eric_G" wrote:

Unless most "right" or "left" functions in which one is looking to select the
"LAST" or the "FIRST" value within a string of characters within a call, I
wish to select the "SECOND TO LAST" value within a string of characters
within a call. IN other words, assume the string of characters of a cell
ends with "Fname Lname". I wish to select "Fname" from the character string
and place it in another column. Any ideas? Much appreciated.

  #4  
Old April 25th, 2010, 05:49 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Find second to last value in cell

Looks like overkill to me, the RIGHT seems superfluous as it seems fine to
just use

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

--

HTH

Bob

"Roger Govier" wrote in message
...
Very nice solution, Teethless Mama!
--
Regards
Roger Govier

Teethless mama wrote:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99))


"Eric_G" wrote:

Unless most "right" or "left" functions in which one is looking to
select the "LAST" or the "FIRST" value within a string of characters
within a call, I wish to select the "SECOND TO LAST" value within a
string of characters within a call. IN other words, assume the string
of characters of a cell ends with "Fname Lname". I wish to select
"Fname" from the character string and place it in another column. Any
ideas? Much appreciated.



  #5  
Old April 25th, 2010, 05:50 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Find second to last value in cell

Oops, my mistake, I missed the OPs request for Second to last :-)

--

HTH

Bob

"Roger Govier" wrote in message
...
Very nice solution, Teethless Mama!
--
Regards
Roger Govier

Teethless mama wrote:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99))


"Eric_G" wrote:

Unless most "right" or "left" functions in which one is looking to
select the "LAST" or the "FIRST" value within a string of characters
within a call, I wish to select the "SECOND TO LAST" value within a
string of characters within a call. IN other words, assume the string
of characters of a cell ends with "Fname Lname". I wish to select
"Fname" from the character string and place it in another column. Any
ideas? Much appreciated.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:43 PM.


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