Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

excel fomula

I need help to write the formular for ship type column.
The excel file has two columns, if the column shipper value doesn't begin with 'C-', or ' F-' or 'S-', then ship type column should be unknown, otherwise will return the first letter of Shipper column.

SHIPPER      SHIP TYPE
PAT                 unknown
C-TH                 C
F-KC                 F
S-DB                 S
........
.......

thanks,
0
HemlockPrinters
Asked:
HemlockPrinters
  • 6
  • 4
  • 2
  • +1
5 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Assuming the Shipper is in column A:

=IF(OR(LEFT(A2,2)="C-",LEFT(A2,2)="F-"LEFT(A2,2)="S-"),LEFT(A2,1),"unknown")

Kevin
0
 
NBVCCommented:
Assuming data starts in A2, try:

=IF(OR(ISNUMBER(SEARCH({"C-","F-","S-"},A2))),LEFT(A2),"unknown")

copied down
0
 
zorvek (Kevin Jones)ConsultantCommented:
More compact:

=IF(OR(LEFT(A2,2)={"C-","F-","S-"}),LEFT(A2,1),"unknown")

Kevin
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
=IF(ISERR(FIND(LEFT(A1,2),"C-F-S-")),"unknown",LEFT(A1))
0
 
NBVCCommented:
or still more compact:

=IF(OR(LEFT(A2,2)={"C-","F-","S-"}),LEFT(A2),"unknown")
0
 
Patrick MatthewsCommented:
"Name that tune!"
0
 
zorvek (Kevin Jones)ConsultantCommented:
Go play with Access. We're busy here.
0
 
Saqib Husain, SyedEngineerCommented:
BarryCade
0
 
zorvek (Kevin Jones)ConsultantCommented:
A more generic solution that looks for any value with a letter followed by a dash followed by two letters:

=IF(ISERR((SEARCH("?-??",A2))),"unknown",LEFT(A2))

Kevin
0
 
NBVCCommented:
Hey Kevin, just bought your book with Jelen/Umlas, to give to my sister in-law... hopefully it'll get her off my back for a while ;)
0
 
zorvek (Kevin Jones)ConsultantCommented:
:-)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Not Umlas, Urtis.
0
 
NBVCCommented:
ooops...   :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now