Split data from 1 field into 2 1

[esteiner]

Hello, please excuse my ignorance.

I would like to split the values of a field called "ManfPartNum" into two fields ("Prefix" and "BaseNum&quot.

I have an Access 97 database of about 50,000 records for which I would like to perform this operation on a weekly basis.

The following is an example record:
ManfPartnum = TMS5220CNL

I would like to create two new fields that contain the pieces of ManfPartNum. The values should be as follows:
Prefix = "TMS"
BaseNum = "5220CNL"

"Prefix" should be all of the alpha characters until the first number. The first number and the rest of the characters should be part of "BaseNum".

Any help would be appreciated

Thank you,

Charlie

 

[SQLBI]

Hi,

If your ManfPartnum are always in the same format i.e. always prefixed by 3 letters you could use

UPDATE tblYourTable SET Prefix = Left(ManfPartnum, 3), BaseNum = Right(ManfPartnum, Len(ManfPartnum) - 3);

It's more complicated if they vary but still possible.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[esteiner]

I should have been more clear. The ManfParNum is not always prefixed by three letters. It varies between 0 and 4 letters.

Any ideas? Thank you very much for your help.

-Charlie

 

[kphu]

You can throw in an if then else statement to check the first 4 characters like LLLL*

then

UPDATE tblYourTable SET
Prefix = Left(ManfPartnum, 4), BaseNum = Right(ManfPartnum, Len(ManfPartnum) - 4)

else

UPDATE tblYourTable SET Prefix = Left(ManfPartnum, 3), BaseNum = Right(ManfPartnum, Len(ManfPartnum) - 3);

 

[johnnygeo]

example: ManfPartnum = "TMS5220CNL"

Use str & value to extract the first all-numeric sequence of the part number: str(value(ManfPartnum)) = "5220"

Use Instr to return the number of letters preceding the numeric string: Instr(ManfPartnum,str(value(ManfPartnum)),1) = 3

Use left & right to split into prefix and basenum: left(ManfPartnum,Instr(ManfPartnum,str(value(ManfPartnum)),1) = "TMS"

right(ManfPartnum,len(ManfPartnum)-Instr(ManfPartnum,str(value(ManfPartnum)),1) = "5220CNL"

rgds,
Johnny Geo