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


"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.

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.

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.

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.

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.

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.



"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.

That is sweet. Thank you.

"Teethless mama" wrote:

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.



"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.