Changing a text entry format

[Guest]

I am consolidating some rather large databases that are all indexed with a
medical record number in a specific format. The format I am using is a letter
followed by 9 digits, as follows:

A#########

The source of the data I am attempting to incorporate is also indexed with
the medical record number, but in a straight 12 digit format with the last 9
being the same. It is as follows:

############

I need to convert the second format to the first. Basically drop the first 3
digits and add the letter "N" to the front.

Thanks to Tom Ogilvy, I can now compare the data bases and incorporate the
matching records. But first I must convert the numbers to matching formats.

Any help would be greatly appreciated,

WillRn

 

[Otto Moehrbach]

WillRn
One way:
Sub ConvertFormat()
Dim i as range
Dim MyRange as range
Set MyRange = 'Define the range you want to convert
For each i in MyRange
i = "N" & Right(i, 9)
Next i
End Sub

HTH Otto