Find data in a field following a specific character

[Guest]

I have a field in a table with data added to serial numbers of different
structures like 12-12.123456 or 12.12345678. The only common is that every
number has a period in it.
I need to separate the portion following the period in a new field.
Is there a function that lets me identify the period or any character in the
middle of a string and select the part folowing this character. Your help is
greatly appreciated.

 

[Guest]

Use a combination of the Mid and Instr functions. The former, without a
length argument returns all characters from the specified position to the end
of the string expression; the latter returns the position of a string within
a string, so putting the two together you'd use:

Mid([YourField],Instr([YourField],".")+1)

Ken Sheridan
Stafford, England

 

[Tom Ellison]

Dear Obelix:

The InStr() function does this. Details in help.

Searching for the period, it would give 6 in the first case and 3 in the
second. Then use the LEFT function as in

LEFT(ColumnName, InStr(ColumnName, ".") - 1)

to obtain the left portion, and

MID(ColumnName, InStr(ColumnName, ".") + 1)

to get the rest.

Does that help?

Tom Ellison

 

[Guest]

Thank you much Ken, that's what I needed.

Use a combination of the Mid and Instr functions. The former, without a
length argument returns all characters from the specified position to the end
of the string expression; the latter returns the position of a string within
a string, so putting the two together you'd use:

Mid([YourField],Instr([YourField],".")+1)

Ken Sheridan
Stafford, England

I have a field in a table with data added to serial numbers of different
structures like 12-12.123456 or 12.12345678. The only common is that every
number has a period in it.
I need to separate the portion following the period in a new field.
Is there a function that lets me identify the period or any character in the
middle of a string and select the part folowing this character. Your help is
greatly appreciated.

 

[Guest]

Thanks Tom, that's what I needed.

Dear Obelix:

The InStr() function does this. Details in help.

Searching for the period, it would give 6 in the first case and 3 in the
second. Then use the LEFT function as in

LEFT(ColumnName, InStr(ColumnName, ".") - 1)

to obtain the left portion, and

MID(ColumnName, InStr(ColumnName, ".") + 1)

to get the rest.

Does that help?

Tom Ellison