• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Extract specific portion of string

I have a field with sample data:

Mr. Joe Smith '78, MS '92
Mr. Pete Jones '92, MBA '01, PHD '07
Mr. Bill O'Connor '78
Ms. Jen R. Sanders '52, MFA '61

I need a formula that gives me, respectively:

'78, MS '92
'92, MBA '01, PHD '07
'78
'52, MFA '61

Any suggestions?  Is there a way to scan the string from left to right and grab the first number found and everything after it?
0
Reader999
Asked:
Reader999
1 Solution
 
mlmccCommented:
Try this formula.  It seems to work so long as there is only 1 ' in the name

mlmcc
Local StringVar strName;
Local NumberVar aposLoc;
strName := {YourStringField};

aposLoc := InStr(strName,"'");
If aposLoc > 0 then
(
   strName := Mid(strName,aposLoc);
   If Isnumeric(Mid(strName,2,1)) then
      strName 
   Else
   (
      aposLoc := InStr(2,strName,"'");
      If aposLoc > 0 then
      (
         strName := Mid(strName,aposLoc);
         strName 
      )
      Else
         ""
   )
)
Else
   ""

Open in new window

0
 
Reader999Author Commented:
Works!  Thanks!
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

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now