How can I split this data (GRM219E41H104MA01D+C01) at the + sign?

A

Angel

I need to separate this data into two columns. What formula would I use to do
this in a query? I actually do not need the information to the right of the +
sign (including the +).
I could bring it into Excel and do a text to columns but since I am making
this query for another user I would like to simplify the process as more
steps are needed following this procedure.

Thanks for any help you can give.
 
J

John Spencer

Use the following expression to return everything after the plus sign

Mid(TheField, Instr(1, TheField & "+",""+")+1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

Angel

OK, I can split the data using the information I found in another thread
using this formula
Product #: Left([MyField], InStr([MyField], "-") - 1) - Thanks to Doug
Steele, Microsoft Access MVP!

However, the column also contains numbers that do not contain the + sign in
them so it will not pull those numbers over to the new cloumn.
What do I to make sure all part numbers are brought over minus only any
numbers or characters following a + sign?

Examples:
PLT09HN2003R0P1B+-006
PRF18BA471QB1RB
5ER-22-22-451
 
M

Marshall Barton

Angel said:
I need to separate this data into two columns. What formula would I use to do
this in a query? I actually do not need the information to the right of the +
sign (including the +).
I could bring it into Excel and do a text to columns but since I am making
this query for another user I would like to simplify the process as more
steps are needed following this procedure.
Click to expand...


Use a calculated field in your query:

Stripped: IIf(InStr(thefield, "+")>0, Left(thefield,
InStr(thefield, "+") - 1), thefield)
 
J

Jerry Whittle

Left("GRM219E41H104MA01D+C01",instr("GRM219E41H104MA01D+C01","+")-1)

Mid("GRM219E41H104MA01D+C01",instr("GRM219E41H104MA01D+C01","+")+1)

In a query:

TheLeft: Left([TheFieldName],InStr(The[FieldName],"+")-1)

TheRight: Mid([TheFieldName],InStr(The[FieldName],"+")+1)
 
J

John Spencer

Use the following expression

IIF([TheField] Like "*+*", Mid([TheField], Instr(1, [TheField]
,"+")+1),[TheField])


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Angel said:
OK, I can split the data using the information I found in another thread
using this formula
Product #: Left([MyField], InStr([MyField], "-") - 1) - Thanks to
Doug
Steele, Microsoft Access MVP!

However, the column also contains numbers that do not contain the + sign
in
them so it will not pull those numbers over to the new cloumn.
What do I to make sure all part numbers are brought over minus only any
numbers or characters following a + sign?

Examples:
PLT09HN2003R0P1B+-006
PRF18BA471QB1RB
5ER-22-22-451



Angel said:
I need to separate this data into two columns. What formula would I use
to do
this in a query? I actually do not need the information to the right of
the +
sign (including the +).
I could bring it into Excel and do a text to columns but since I am
making
this query for another user I would like to simplify the process as more
steps are needed following this procedure.

Thanks for any help you can give.
Click to expand...
Click to expand...
 
A

Angel

Thanks Jerry with a few modifications this worked like a charm! I needed to
get the numbers to the left of the plus so I finally ended up using:

Left([FieldName],InStr(1,[FieldName] & "+","+")-1)

Fantastic Many Thanks!
Angel

Jerry Whittle said:
Left("GRM219E41H104MA01D+C01",instr("GRM219E41H104MA01D+C01","+")-1)

Mid("GRM219E41H104MA01D+C01",instr("GRM219E41H104MA01D+C01","+")+1)

In a query:

TheLeft: Left([TheFieldName],InStr(The[FieldName],"+")-1)

TheRight: Mid([TheFieldName],InStr(The[FieldName],"+")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Angel said:
I need to separate this data into two columns. What formula would I use to do
this in a query? I actually do not need the information to the right of the +
sign (including the +).
I could bring it into Excel and do a text to columns but since I am making
this query for another user I would like to simplify the process as more
steps are needed following this procedure.

Thanks for any help you can give.
Click to expand...
Click to expand...
 
J

John W. Vinson

OK, I can split the data using the information I found in another thread
using this formula
Product #: Left([MyField], InStr([MyField], "-") - 1) - Thanks to Doug
Steele, Microsoft Access MVP!

However, the column also contains numbers that do not contain the + sign in
them so it will not pull those numbers over to the new cloumn.
What do I to make sure all part numbers are brought over minus only any
numbers or characters following a + sign?
Click to expand...

Just append an extra +:

Product #: Left([MyField] & "+", InStr([MyField] & "+", "+") - 1)

John W. Vinson [MVP]
 
Top