Label expression formula question

267
7
10-26-2012 07:56 AM
QuinnMcCarthy
New Contributor
using vb script is there a way to extract the number 1 from a field in the attribute table that has 'County Road 1'? I hope that is not confusing.

Thanks much.

Quinn
Koochiching County  MN
Tags (2)
0 Kudos
7 Replies
QuinnMcCarthy
New Contributor
To make this more clear I need to extract everything to the right of the rihjt most space. Becasue some roads are 1E or 1S.

Thanks

Quinn
0 Kudos
AnthonyGiles
Frequent Contributor II
Quinn,

Do you just want to remove the number 1 when the label is shown, if so use the replace function:

Function FindLabel ( [Road_Name] )
If Not IsNull([Road_Name]) Then
NewStr = Replace([Road_Name]," 1E","")
NewStr = Replace(NewStr," 1S","")
  FindLabel = NewStr
End If
End Function

Regards

Anthony
0 Kudos
RichardFairhurst
MVP Honored Contributor
To make this more clear I need to extract everything to the right of the rihjt most space. Becasue some roads are 1E or 1S.

Thanks

Quinn


If there are always just 1 or 2 characters at the end of the value with a leading white space, in VB Script you can use the Right function with the LTrim function to get the last non-white space character or characters:

LTrim(Rigth(myFieldValue, 2))

To get all of the characters to the left of the last 1 or 2 characters excluding a trailing white space use the Left Function, the Len function and the RTrim function as follows:

RTrim(Left(myFieldValue, Len(myFieldValue) - 2))

Both of these are simple label expressions.
0 Kudos
QuinnMcCarthy
New Contributor
Thanks Richard and Anthony. I will give them a try.

I think Richards answer might be what I am looking for. We will see.

Quinn
0 Kudos
AnthonyGiles
Frequent Contributor II
Quinn,

The only problem I see that could occur with Richards suggestion is that all roads will have to end with 1E or 1S otherwise you will cut the last two characters off the name,

Regards

Anthony
0 Kudos
KenBuja
MVP Honored Contributor
You can use the Split function to put the words into an array and just retrieve the last item in the array.

Function FindLabel ( [Road_Name] )
  If Not IsNull([Road_Name]) Then
     arrString = Split([Road_Name])
     FindLabel = arrString(UBound(arrString))
  End If
End Function
0 Kudos
AnthonyGiles
Frequent Contributor II
Quinn,

Sorry, I think I missed understood what you are trying to achieve, I read it that you have road names which end in 1E or 1S and you wanted to remove them on your labels, i.e:

'Pinewood Road 1E' would be just 'Pinewood Road'

If you want everything right of your last space in the attribute value use:

if InStrRev(txt," ") > 0 Then
right([Road_Name],len([Road_Name]) - InStrRev([Road_Name]," "))
End If

everything left of your last space use:

if InStrRev(txt," ") > 0 Then
left([Road_Name],InStrRev([Road_Name]," "))
End If



Regards

Anthony
0 Kudos