IF statement that looks at part of the contents of a cell.
 

I have a need to make decisions based on the content of a cell that
contains an alphanumeric string such as P8072A. I want the ability to
look at this cell and take an action based on what the first letter in
the string is. For example, If the string is P8072A, I want cell G5 to
be 2.5, and if the string is Y2898B then I want cell G5 to be 3.0
(because the first letter changed from P to Y). Is there a way to have
excel look at letters in a cell to make logical decisions?

IF statement that looks at part of the contents of a cell.
 

Hi Jon,

'=============
Public Sub TestIt()
Dim Rng As Range
Dim rcell As Range

Set rcell = Range("A1") '<<==== CHANGE

If FirstAlpha(rcell.Value) = "P" Then
Range("G5").Value = 2.5
ElseIf FirstAlpha(rcell.Value) = "Y" Then
Range("G5").Value = 3
Else
'do something else?
End If
End Sub

'-----------------

Public Function FirstAlpha(sStr As String)
Dim Re As Object

Set Re = CreateObject("VBScript.RegExp")
Re.IgnoreCase = True
Re.Global = True
Re.Pattern = "\d"

FirstAlpha = Left(Re.Replace(sStr, vbNullString), 1)

End Function
'<<=============


---
Regards,
Norman



"Jon" wrote in message
oups.com...
I have a need to make decisions based on the content of a cell that
contains an alphanumeric string such as P8072A. I want the ability to
look at this cell and take an action based on what the first letter in
the string is. For example, If the string is P8072A, I want cell G5 to
be 2.5, and if the string is Y2898B then I want cell G5 to be 3.0
(because the first letter changed from P to Y). Is there a way to have
excel look at letters in a cell to make logical decisions?

IF statement that looks at part of the contents of a cell.
 

I think I may have it.... =IF(MID(C10,1,1)="P","True","False")
MID returns the first character in cell C10. I compare it to "P" and it
gives a true or false.

IF statement that looks at part of the contents of a cell.
 

if left(activecell,1) = "P" then

elseif left(activecell,1) = "Y" then

end if

--
Regards,
Tom Ogilvy


"Jon" wrote in message
oups.com...
I have a need to make decisions based on the content of a cell that
contains an alphanumeric string such as P8072A. I want the ability to
look at this cell and take an action based on what the first letter in
the string is. For example, If the string is P8072A, I want cell G5 to
be 2.5, and if the string is Y2898B then I want cell G5 to be 3.0
(because the first letter changed from P to Y). Is there a way to have
excel look at letters in a cell to make logical decisions?