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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
..
 
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.
Click to expand...


Use a calculated field in your query:

Stripped: IIf(InStr(thefield, "+")>0, Left(thefield,
InStr(thefield, "+") - 1), thefield)
 
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)
 
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...
 
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...
 
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]
 
Back
Top