Need to sort part numbers, sometimes ignoring leading letters
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.
"SDP" wrote:
I think the "TE" prefix is lost then though, right?
"Teethless mama" wrote:
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.
"SDP" wrote:
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)?
"Teethless mama" wrote:
create a helper column in columnB
In B2: =SUBSTITUTE(A2,"TE","")
copy down, then sort the columnB
"SDP" wrote:
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.
|