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  

IF and Right Functions



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 03:33 PM posted to microsoft.public.excel.worksheet.functions
Elaine
external usenet poster
 
Posts: 195
Default IF and Right Functions

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I would
like to display the last 3 or 4 characters in another column. I tried using
the IF and Right functions and have not solved it. I thank you in advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

  #2  
Old April 20th, 2010, 03:41 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default IF and Right Functions

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I would
like to display the last 3 or 4 characters in another column. I tried using
the IF and Right functions and have not solved it. I thank you in advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

  #3  
Old April 20th, 2010, 04:03 PM posted to microsoft.public.excel.worksheet.functions
Elaine
external usenet poster
 
Posts: 195
Default IF and Right Functions

WOW! Thank you for your quick response to my question. It works like a champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I would
like to display the last 3 or 4 characters in another column. I tried using
the IF and Right functions and have not solved it. I thank you in advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

  #4  
Old April 20th, 2010, 05:08 PM posted to microsoft.public.excel.worksheet.functions
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default IF and Right Functions

This section is used to find out how many dashes you have
LEN(""&A1)-LEN(SUBSTITUTE(A1,"-","")
The total length - the length with the dashes removed.

Now that we know how many dashes we have we can use substiture to change the
last dash into some other characters that will be unique to the text.

SUBSTITUTE(A1,"-","^^", 'number of dashes (last dash)

So now we have a uniqe character string just before the ending suffix. We
can use the find function to determine where the unique text is

FIND("^^",

Which tells us how may characters in to find the suffix.

Now we just use mid to grab the characters from where the last das was until
the end. The 1024 just signifies that we will grab up to character 1024.

--
HTH...

Jim Thomlinson


"Elaine" wrote:

WOW! Thank you for your quick response to my question. It works like a champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I would
like to display the last 3 or 4 characters in another column. I tried using
the IF and Right functions and have not solved it. I thank you in advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

  #5  
Old April 20th, 2010, 05:19 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 516
Default IF and Right Functions

Elaine,
I am trying a new way to reply to questions (where appropriate).
I have made a file that takes Mike's formula apart step-by-step starting
from the inside.
This will help you see how it works.
The file is housed at
http://cid-162119ff97286948.skydrive...extExtract.xls
This is the Microsoft Skydrive locale where one can store private and public
files
Just copy the URL into your browser
I would be interested in your reaction to getting answers like this
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Elaine" wrote in message
...
WOW! Thank you for your quick response to my question. It works like a
champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I
would
like to display the last 3 or 4 characters in another column. I tried
using
the IF and Right functions and have not solved it. I thank you in
advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

  #6  
Old April 20th, 2010, 05:31 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default IF and Right Functions

Glad i could help

Can you give me a break-down of the formula you gave?


Of course.

First, to summarise it relies on not finding the string "^^" in the string
which is a reasonably safe bet.

Simplified and for your first string the formula evaluates to this and I
think that requires no further explanation except how do we get the number 8
which is the position of the character of the string you want. 1024 is just
the number of characters to rteurn to ensure we get them all

=MID(A1,8,1024)

This part of the formula if put in a cell on its own returns the 8
=FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1

But How? the formula substitues every instance of - with ^^ and remember
that's the gamble. If there's already an instance of ^^ where in a mess but
that's unlikely


This bit
SUBSTITUTE(A1,"-","^^")
creates a temporary new string that looks like this
30^^179^^TC1

This bit tells it to find ^^ in this new string
=FIND("^^",

but we need to know which instance to find (we want the second)

this bit tests the length of the string with - substituted with nothing ""
compared to the length of the original string in A1
LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))
from this we now know there are 2 instances of - in the full string

so we tell the formula to look for the second instance of ^^ which returns 7
add 1 and we have our number 8
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

WOW! Thank you for your quick response to my question. It works like a champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I would
like to display the last 3 or 4 characters in another column. I tried using
the IF and Right functions and have not solved it. I thank you in advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

  #7  
Old April 20th, 2010, 06:19 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default IF and Right Functions

Bernard,

I would be interested in your reaction to getting answers like this


It's a darn sight clearer than the explanation I gave to how my formula worked
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Bernard Liengme" wrote:

Elaine,
I am trying a new way to reply to questions (where appropriate).
I have made a file that takes Mike's formula apart step-by-step starting
from the inside.
This will help you see how it works.
The file is housed at
http://cid-162119ff97286948.skydrive...extExtract.xls
This is the Microsoft Skydrive locale where one can store private and public
files
Just copy the URL into your browser
I would be interested in your reaction to getting answers like this
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Elaine" wrote in message
...
WOW! Thank you for your quick response to my question. It works like a
champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I
would
like to display the last 3 or 4 characters in another column. I tried
using
the IF and Right functions and have not solved it. I thank you in
advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23

.

  #8  
Old April 20th, 2010, 07:22 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 563
Default IF and Right Functions

Hi Bernard
To receive answers like that would be great, a treasure house to keep for
reference.
It would be much more work for you!!!
Regards
John
"Bernard Liengme" wrote in message
...
Elaine,
I am trying a new way to reply to questions (where appropriate).
I have made a file that takes Mike's formula apart step-by-step starting from
the inside.
This will help you see how it works.
The file is housed at
http://cid-162119ff97286948.skydrive...extExtract.xls
This is the Microsoft Skydrive locale where one can store private and public
files
Just copy the URL into your browser
I would be interested in your reaction to getting answers like this
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Elaine" wrote in message
...
WOW! Thank you for your quick response to my question. It works like a champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I would
like to display the last 3 or 4 characters in another column. I tried
using
the IF and Right functions and have not solved it. I thank you in advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23


  #9  
Old April 21st, 2010, 09:24 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 516
Default IF and Right Functions

Thanks for comment. Not much work since in many cases I actually make a
workbook to ensure I have no typo errors in formulas
cheers
Bernard

"John" wrote in message
...
Hi Bernard
To receive answers like that would be great, a treasure house to keep for
reference.
It would be much more work for you!!!
Regards
John
"Bernard Liengme" wrote in message
...
Elaine,
I am trying a new way to reply to questions (where appropriate).
I have made a file that takes Mike's formula apart step-by-step starting
from the inside.
This will help you see how it works.
The file is housed at
http://cid-162119ff97286948.skydrive...extExtract.xls
This is the Microsoft Skydrive locale where one can store private and
public files
Just copy the URL into your browser
I would be interested in your reaction to getting answers like this
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Elaine" wrote in message
...
WOW! Thank you for your quick response to my question. It works like a
champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I
would
like to display the last 3 or 4 characters in another column. I tried
using
the IF and Right functions and have not solved it. I thank you in
advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23


  #10  
Old April 23rd, 2010, 12:34 AM posted to microsoft.public.excel.worksheet.functions
Elaine
external usenet poster
 
Posts: 195
Default IF and Right Functions

Mike, Jim, RagDyeR, and John,

All of you are GREAT! I thank each and one of you for your input.

I'm so glad I have "background" support.

Again, my many thanks for all of your efforts and continued support.

Respectfully,

Elaine

"Bernard Liengme" wrote:

Thanks for comment. Not much work since in many cases I actually make a
workbook to ensure I have no typo errors in formulas
cheers
Bernard

"John" wrote in message
...
Hi Bernard
To receive answers like that would be great, a treasure house to keep for
reference.
It would be much more work for you!!!
Regards
John
"Bernard Liengme" wrote in message
...
Elaine,
I am trying a new way to reply to questions (where appropriate).
I have made a file that takes Mike's formula apart step-by-step starting
from the inside.
This will help you see how it works.
The file is housed at
http://cid-162119ff97286948.skydrive...extExtract.xls
This is the Microsoft Skydrive locale where one can store private and
public files
Just copy the URL into your browser
I would be interested in your reaction to getting answers like this
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Elaine" wrote in message
...
WOW! Thank you for your quick response to my question. It works like a
champ.
Can you give me a break-down of the formula you gave?

Thank you, thank you, thank you!

Elaine

"Mike H" wrote:

Hi,

Try this for a string in a1 and drag down

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"Elaine" wrote:

Hello,

I have part #'s that end in 3 or 4 characters, some with a space. I
would
like to display the last 3 or 4 characters in another column. I tried
using
the IF and Right functions and have not solved it. I thank you in
advanced
for your help on this formula.

Example:
Part # New Column (Result)
30-179-TC1 TC1
30-181-T C2 T C2
30-185-TC23 TC23


.

 




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:15 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.