microsoft access custom vba code to manipulate string

I need help in extracting and formatting a column that contains text.  The output needs to be:

RGXXXXXX  (RG followed by a 6 digit number).

The data is stored in various flavors.  I have attached a list on some of the variants.  So in the example below:

RGO: 277803
S/N: 58029644

The output should be RG277803 after running some public function that I can call in a MS Access query.  I am new to this and could really use some expert help.

Thanks.
Book1.xls
sxxguptaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MarvinM80Connect With a Mentor Commented:
Your sample is in Excel, but you want an Access function. Right?
First, you want to determine the format that your input is in. It appears that we can differentiate on the space character that occurs in the 3rd position or the 4th position or not at all. We can use a CASE statement for this.

Dim strIn As String
Dim strOut As String

strIn = Your Input String

Select Case InStr(1,strIn," ")

CASE 3
strOut = Mid(strIn, 1, 2) & Mid(strIn, 4, 6)

CASE 5
strOut = Mid(strIn, 1, 2) & Mid(strIn, 6, 6)

CASE Else
strOut = Mid(strIn, 1, 8) 

End Select

Open in new window

0
 
sxxguptaAuthor Commented:
Thx.
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.

All Courses

From novice to tech pro — start learning today.