cusip (number and text) in same cell- How do use in excel?

[John K]

I need to define cusips in excel in an if/nested if statement

Example

Cell A1=005482J86
Cell A2=0162483G9

How do i use and if/or logic statement to make a true statement equal "Muni
bond" (cell b1) or "Corp Bond"(cell b2)

I cannot get excel to recognize any logic statement since a cusip has
numbers and text!!

Please help!
Thanks in advance !!

 

[Biff]

Hi!

The only "stand-out" difference between the 2 samples is
that one contains a "J" and one contains a "G".

Does J denote a municipal bond and G denote a corporate
bond?

If you could explain how to distinguish one from another
it should be relatively easy to find a solution.

Biff

 

[John K]

Biff

Thanks for your reply, I did not explain myself very well, I will try expand.

A cusip is random so to speak, unfortunately there is no specific trait that
allows me to determine if a muni or corp, so I need a way to specifically
find the cusip value (I am expecting a very long series of nested if's or
"or" logic statements, but this is ok) My problem appears to be that when
Excel has a cell value, that includes numbers and text, I cannot use a
regular number logic feature or a "quoted" text logic feature to recognize
the specific cell. Since the cusips are random, I cannot search for a
specific letter as sometimes the same letter is in both cusips (i.e.
005482J96 and 01648JJJ6 , could either be a muni or a corp bond)

The only definite characteristic is that cusips tend to have numbers in the
first four to five digits, a total of 9 digits, and the last three to four
digits are a mix of numbers and letters randomly.

As an example, my thoughts were:

=if(a1="005482J96","Muni Bond","Not Muni Bond")
or =if(a1=005482J96,"Muni Bond","Not Muni Bond")

This does not work, as Excel does not like the number text mixed cusip in
cell A1.

As a thought, I may be able to search using the first four to five numbers
individually in the cusip, Is there a way to have excel just look at the
first 5 digits in the cell, that is 9 in length??

Your help would be greatly appreciated !!

Thanks

John

 

[Duke Carey]

John -

Excel has no problems with mixed text & digits - it treats the entry as text
and can match it just fine. However, if there are spaces in the cell before
or after the CUSIP, Excel will incorporate the spaces into the test, too,
such that "T" <> "T " <> " T"

If you get your CUSIPs from an external data source it is entirely possible
that data source pads the CUSIPs with spaces. Excel has a TRIM() function
that will eliminate spaces, so that TRIM("T") = TRIM("T ") = TRIM(" T")

You cannot nest IF() statements indefinitely. The limit is something like 7
or 9 nested IF()s. You're better off building a lookup table with all your
CUSIPs in one column and the descriptive text in the adjacent column. Then
your formula would be
=VLOOKUP(TRIM(cell with CUSIP), lookup_range, 2, false)

Duke

 

[Biff]

Hi!

Is there a way to have excel just look at the
first 5 digits in the cell, that is 9 in length??

Yes, you can do that but what would you be looking for?
You need to define a specific characteristic.

Biff