I'm working in Microsoft Access 2010 and I have a table and column [tblData]![Associate Name] with data formatted like Smith, John (123456). Obviously, this shows last name, first name, and the employee ID number (which can vary between 5 to 6 digits). My issue is that I need to extract the employee ID number from this string and I'm struggling to use the correct combination of Left, Right, Mid with the InStr function.
Asked
Active
Viewed 2,250 times
1
-
1Why are you storing the data like this at all? This is in essence a calculated column, which is bad design. – random_answer_guy Aug 01 '16 at 19:36
-
Unfortunately, this isn't my data. I was just asked to manipulate it lol – N.Ha Aug 01 '16 at 19:46
1 Answers
4
Use InStr() to find the position of the ( character.
Next use Mid() to extract the substring starting one character after the (
And then Val() will give you a number from the remaining digits; it ignores anything after the last digit.
If you want the result as a string instead of number, you can use CStr() to convert it.
Here is an example from the Immediate window:
Associate_Name = "Smith, John (123456)"
? InStr(1, Associate_Name, "(")
13
? Mid(Associate_Name, InStr(1, Associate_Name, "("))
(123456)
? Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1)
123456)
? Val(Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1))
123456
? CStr(Val(Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1)))
123456
HansUp
- 95,961
- 11
- 77
- 135
-
Also see whether [OnlyDigits()](http://stackoverflow.com/a/12570564/77335) would be useful for your situation. – HansUp Aug 01 '16 at 19:22
-
Ohhh ok that makes so much sense on what the through process is when doing this kind of thing. Thank you for your time! – N.Ha Aug 01 '16 at 19:31
-
Yes, I thought the code example would explain it better than my words did. :-) You're welcome. – HansUp Aug 01 '16 at 19:32