[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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

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.

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