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

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
0
sxxgupta
Asked:
sxxgupta
1 Solution
 
MarvinM80Commented:
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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