String which should start from the alphabetic

[tabisen1]

Dear All,

Very good after noon to all i am Creating the QVW for the count of truck round in which i am facing some issues for example.

truck no. should be

mh04ab1234

mwv123

ect,

I data base the truck no. is not as per the format. I want to create it like above format. I have  cleared the special charater in between the truck no and created the single string which show like this

Previous

mh04/ ab 1234

123 mwv-123

mh-04 ab1234 / 5678

mwv 123 (9876)

9876 / mwv123

mwv 123 (9876/234)

9876/ 234- mwv123

current by using the

PurgeChar([Truck no],'/, ,-,(,)')

mh04ab1234

123mwv123

mh04ab12345678

mwv1239876

9876mwv123

mwv1239876234

9876234mwv123

I want the above current to modify which will take the starting alphabet and remove the starting numbers and also want to remove the end extra number which is extra number for the reference in give at the end and begining of the truck no.

Regards,

[tabisen1]

Dear Sunny,

No I am not having the List of all truck no.

[Anonymous]

Hi Tarun,

Does your CorrectTruckNo has any limited number of digits at the end.

is there any other field in database which gives truck nos?

Regards

Neetha

[sasiparupudi1]

please provide your data file..but at this stage, there is not enough information available to extract the strings that you are looking for

[tabisen1]

Dear Manish,

Is their any criteria for this

data base	currect Truck No.
mh04/ ab 1234	mh04ab1234
123 mwv-123	mwv123
mh-04 ab1234 / 5678	mh04ab1234
mwv 123 (9876)	mwv123
9876 / mwv123	mwv123
mwv 123 (9876/234)	mwv123
9876/ 234- mwv123	mwv123

[tabisen1]

Dear Sasidhar,

My data file has the field named truck no.

Data file field look like this	Which Should be currect like this
mh04/ ab 1234	mh04ab1234
123 mwv-123	mwv123
mh-04 ab1234 / 5678	mh04ab1234
mwv 123 (9876)	mwv123
9876 / mwv123	mwv123
mwv 123 (9876/234)	mwv123
9876/ 234- mwv123	mwv123

String which should start from the alphabetic

[MK_QSL]

I am close to it but can't get logic behind MWV123 and mh04ab1234.

Why only three letter 123 after MWV and 4 letters after ab???

KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')

[sunny_talwar]

Manish it seems like that there is a / after 1234

mh-04 ab1234 / 5678

mh04ab1234

and ( after 123

mwv 123 (9876)

mwv123

Seems he is looking for things between special characters

[MK_QSL]

If I will consider that then

mh-04 ab1234 / 567 = mh04ab1234

mh04/ ab 1234 = mh04

[MK_QSL · Accepted solution]

Try this...

IF(LEN(KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'))<6,

KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'),

KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'))

[sunny_talwar]

Ya you are right. Some logic is needed here