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

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

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

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

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

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

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

.

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

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

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


.