[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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