Split the values

[editolis]

Hi all!

I have a column which has the values like below:
0-80
10 -11
10- 11
1 - 12

The field is text.

I need to split the values in to 2 part and put into two columns
without "-", eg:

0 | 80
10 | 11
10 | 11
1 | 12

How can we do it? I am confused...

Thank you in advance...

 

[Mike375]

If you do a search on Left(), Right(), Mid(), InStr() and Len() you will have the basis to do what you currently want plus most variations of what you want.

Left extacts from the left, Right from the right and Mid it like Right but only goes across the entry to a nominated position.

InStr is about finding the position of a charcter such a your -. Len counts the number of characters.

 

[editolis]

I Try this and works perfect for the first column.

myfield1: Trim(IIf(InStr([myfield];"-")>0;Left([myfield];InStr([myfield];"-")-1)))

THE PROBLEM IS WITH THE SECOND COLUMN:

myfield2: Trim(IIf(InStr([myfield];"-")>0;Right([myfield];InStr([myfield];"-")-1)))

Some times still have the "-".

What i do wrong?

 

[editolis]

OK Finally this is my solution:

for COLUMN 1:

field1: Trim(IIf(InStr([field];"-")>0;Left([field];InStr([field];"-")-1);[field]))

for COLUMN 2:

field2: Trim(IIf(InStr([field];"-")>0;Right([field];Len([field])-InStr([field];"-"));""))

Thanks all.

 

[Mike375]

Exp1: Left([abc],InStr([abc],"-")-1)

Exp2: LTrim(Right([abc],Len([abc])-InStr([abc],"-")))

 

[MSAccessRookie]

I Try this and works perfect for the first column.

myfield1: Trim(IIf(InStr([myfield];"-")>0;Left([myfield];InStr([myfield];"-")-1)))

THE PROBLEM IS WITH THE SECOND COLUMN:

myfield2: Trim(IIf(InStr([myfield];"-")>0;Right([myfield];InStr([myfield];"-")-1)))

Some times still have the "-".

What i do wrong?

Look up the IsNumeric() Function. Using that function, all of your examples look like this:

1. All characters from the first character up to and not including the first Non-Numeric Character are the first number.
2. All characters from that point up to and not including the next numeric characters should be ignored.
3. The remaining characters are the second number.

Format for display as you wish