pulling certain characters from a string of text
 

I need to look up "certain critera" within a string of characters, then
return that "certain criterea" to a new column.
Some examples of a strings of characters may look like these:
K5J091509001
Sample PO#S881009
K55sample PO
CarrieRJR
TJ5
My "Certain Critera" I have listed on another sheet, named "REP ID"
K5J
S88
K55
RJR
TJ5
How do I pull out the 3 characters of "Certain Criterea" from the string of
text and copy or enter it into a new column?

pulling certain characters from a string of text
 

Hi,
=left(a1,3)

"SaraMack" wrote:

I need to look up "certain critera" within a string of characters, then
return that "certain criterea" to a new column.
Some examples of a strings of characters may look like these:
K5J091509001
Sample PO#S881009
K55sample PO
CarrieRJR
TJ5
My "Certain Critera" I have listed on another sheet, named "REP ID"
K5J
S88
K55
RJR
TJ5
How do I pull out the 3 characters of "Certain Criterea" from the string of
text and copy or enter it into a new column?

pulling certain characters from a string of text
 

Looks like you might have to use several formulas depending on the line/row
you are on.
Assuming your data begins in A2 and is consistant as you listed it you could
do something like this for each row and then copy/paste values into a new
sheet:

K5J091509001 =LEFT(A2,3)
Sample PO#S881009 =MID(A3,SEARCH("#",A3,1)+1,3)
K55sample PO =LEFT(A4,3)
CarrieRJR =RIGHT(A5,3)
TJ5 =TRIM(A6)


Hope that helps.

Frank


"SaraMack" wrote:

I need to look up "certain critera" within a string of characters, then
return that "certain criterea" to a new column.
Some examples of a strings of characters may look like these:
K5J091509001
Sample PO#S881009
K55sample PO
CarrieRJR
TJ5
My "Certain Critera" I have listed on another sheet, named "REP ID"
K5J
S88
K55
RJR
TJ5
How do I pull out the 3 characters of "Certain Criterea" from the string of
text and copy or enter it into a new column?

pulling certain characters from a string of text
 

It's never consistant. This is the problem I'm having! Sales Reps enter
their 3 character id with a bunch of other junk as a reference. I just need
their 3 letter Id pulled out to do a vlookup with that information.

like RepID K5J could be nested anywhere within a string of text
12/10/09K5J PO#
K5J121009001
121009001K5J
Sample PO#K5J
KenREPIDK5J1210

and I have 100's of REP ID's listed on another sheet.

Do you know what I mean?
"FrankWood" wrote:

Looks like you might have to use several formulas depending on the line/row
you are on.
Assuming your data begins in A2 and is consistant as you listed it you could
do something like this for each row and then copy/paste values into a new
sheet:

K5J091509001 =LEFT(A2,3)
Sample PO#S881009 =MID(A3,SEARCH("#",A3,1)+1,3)
K55sample PO =LEFT(A4,3)
CarrieRJR =RIGHT(A5,3)
TJ5 =TRIM(A6)


Hope that helps.

Frank


"SaraMack" wrote:

I need to look up "certain critera" within a string of characters, then
return that "certain criterea" to a new column.
Some examples of a strings of characters may look like these:
K5J091509001
Sample PO#S881009
K55sample PO
CarrieRJR
TJ5
My "Certain Critera" I have listed on another sheet, named "REP ID"
K5J
S88
K55
RJR
TJ5
How do I pull out the 3 characters of "Certain Criterea" from the string of
text and copy or enter it into a new column?