IF and Right Functions

[Elaine]

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

 

[Mike H]

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]

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

 

[RagDyeR]

If the examples you gave are true for the entire dataset, you might try
these:

=MID(A2,8,10)

=MID(A2,SEARCH("-",A2,7)+1,10)

If your data is not as simple as your examples, then of course, Mike's
formula will work for the more complex part numbers.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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

 

[Jim Thomlinson]

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.

 

[Bernard Liengme]

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.live.com/self.aspx/.Public/TextExtract.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

 

[Mike H]

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.

 

[Mike H]

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.

 

[John]

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]

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

 

[Elaine]

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