Need to sort part numbers, sometimes ignoring leading letters

  • Thread starter Thread starter SDP
  • Start date Start date
S

SDP

Hello.
I have many part numbes to sort. The problem is that some part numbers are
all numbers, and some part numbers begin with: TE
I'd like to ask Excel to ignore the TE when sorting, and only sort by the
number component, but still keep the TE. Any way to do that? I can do it
VERY clumsily with Text To Columns, but this is for a co-worker little Excel
experience, and the list gets added to daily.

Have this:
198919-001
198960-001
198962-001
TE198580-1
TE198763-7

Need this:
TE198580-1
TE198763-7
198919-001
198960-001
198962-001
TE199014-1

I'd appreciate any suggestion, thanks.
 
That was poorly written, sorry. Redo:
Hello.
I have many part numbes to sort. The problem is that some part numbers are
all numbers, and some part numbers begin with: TE
I'd like to ask Excel to ignore the TE when sorting, and only sort by the
number component, but still keep the TE. Any way to do that? I can do it
VERY clumsily with Text To Columns and concantenate after, but this is for a
co-worker with little Excel experience. The list gets added to daily.

Have this:
198919-001
198960-001
198962-001
TE198580-1
TE198763-7
TE199014-1

Need this:
TE198580-1
TE198763-7
198919-001
198960-001
198962-001
TE199014-1

I'd appreciate any suggestion, thanks.
 
create a helper column in columnB

In B2: =SUBSTITUTE(A2,"TE","")
copy down, then sort the columnB
 
Thanks - that takes the Text To Columns part out of it, which is nice.
However, I need the "TE" to remain intact. Is there a way to put it back on
(on the right part numbers)?
 
Assuming your data in columnA, then use helper column in columnB then use my
formula I provided in the previous post, copy the formula down. Select
columnA and columnB then sort by columnB, then delete columnB.
 
Is there an Excel function that would be equivalent to the word "ignore" ?
Like: sort these numbers, ignore (but keep) "TE"

So far, I can only accomplish this sort by:
1. Replace "TE" with "TE|"
2. Text To Columns with "|" as the delimiter
3. Sort BOTH columns so the "TE" stays with its part number
4. Concantenate the "TE" back on
5. Copy, paste values only

I just thought there could be a better way.
 
I think the "TE" prefix is lost then though, right?

No. The original data are never change the "TE" are always there. Just do
exactly what I provided you in the previous post.
 
That is sweet. Thank you.

Teethless mama said:
No. The original data are never change the "TE" are always there. Just do
exactly what I provided you in the previous post.
Click to expand...
 
Back
Top