Serach/Replace with wildcards

bluto

New Member
Joined
Feb 4, 2005
Messages
43
Given these cels in col A:

IAD:a10-1
IAD:a10-10
IAD:a10-11
IAD:a10-12
IAD:a10-13
IAD:a10-14
IAD:a10-15
IAD:a10-16
IAD:a10-17
IAD:a10-18
IAD:a10-19
IAD:a10-2
IAD:a10-20
IAD:a10-21
IAD:a10-22
IAD:a10-23
IAD:a10-24
IAD:a10-25
IAD:a10-3
IAD:a10-4
IAD:a10-5
IAD:a10-6
IAD:a10-7
IAD:a10-8
IAD:a10-9
IAD:a1-1
IAD:a1-10
IAD:a1-11
IAD:a11-1
IAD:a11-10
IAD:a11-11

When sorted, notice that IAD:a10-1 comes before IAD:a1-1, and IAD:a10-2 comes well after

IAD:a10-1. I need to successfully sort with results:

IAD:a1-1
(thru)
IAD:a1-25
(then)
IAD:a2-1 (thru IAD:a2-25)
(up to)
IAD:a18-1 (thru IAD:a18-25)

I tried to search/replace IAD:a1-1 to IAD:a01-01 (assuming excel expects "01" to be smaller

than "1") with:
search IAD:?1-?1 replace IAD:?01-?01
but the results were "IAD:?01-?01" (with question mark)

How do I accomplish this? I'm a novice, so please be gentle... :)
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Assuming all your data should have 10 characters, as your posted data shows, here is one way to do it, in four steps:

Assuming your data is in column C, starting in row 2:

1. Insert two new temporary columns D and E. Enter this formula in cell D2:
=IF(MID(C2,7,1)="-", LEFT(C2,5)&"0"&MID(C2,6,LEN(C2)-5),C2)
and copy down. This will change the first a1, a2, etc. into a01, a02, etc.

2. Enter this formula in E2:
=IF(LEN(D2)=9, LEFT(D2,8)&"0"&MID(D2,9,1),D2)
and copy down. This will change the part after the dash, from -1, -2, etc to -01, -02, etc.

3. Highlight column E, do a Copy, click on C2, then on Edit, Paste Special, then on the option button for Values, and, finally, on OK.

4. You may now safely delete the temporary columns D and E, and proceed with your sorting.

P.S. As to how Excel sorts... Excel sorts in the way computers have sorted since their inception, according to the ASCII code. The ASCII (American Standard Code for Information Interchange) system, created for use in telegraph, was adapted to represent the various characters and actions that take place with computers and printers. If interested, send me a PM with your email, and I will send you my ASCII2.EXE file that shows the 127 ASCII symbols, 0-127, plus the standard IBM Extended Characters, 128-255.
 

bluto

New Member
Joined
Feb 4, 2005
Messages
43
Thanks, RalphA! That did the trick! I figured it would take some VB or such to fix this, and this worked like a charm! I appreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top