How to extract only badge numbers from such a column?

J

Jaleel

Excel 2003 user.

I have 3 columns like this in A,B & C.

1/2/2010 #13837-SALARY PAID FOR JAN-10 120.00
1/2/2010 #13838-SALARY PAID FOR JAN-10 215.00
1/2/2010 TERMINATION SETTLEMENT # 11136 903.47
1/2/2010 TERMINATION SETTLEMENT # 12536 894.20
1/2/2010 TERMINATION SETTLEMENT # 13044 292.49
1/2/2010 TERMINATION SETTLEMENT # 13159 282.78
1/2/2010 TERMINATION SETTLEMENT # 12199 1,169.64
1/2/2010 TERMINATION SETTLEMENT # 11261 2,623.03
1/2/2010 TERMINATION SETTLEMENT # 13630 626.25
1/2/2010 TERMINATION SETTLEMENT # 11840 668.22
1/2/2010 TERMINATION SETTLEMENT # 10572 1,671.76
2/2/2010 TERMINATION SETTLEMENT # 13638 704.32
2/2/2010 TERMINATION SETTLEMENT # 13626 297.88
2/2/2010 TERMINATION SETTLEMENT # 13579 408.92
2/2/2010 TERMINATION SETTLEMENT # 13055 863.25
2/2/2010 TERMINATION SETTLEMENT # 13192 284.67
2/2/2010 TERMINATION SETTLEMENT # 13627 436.33
3/2/2010 TERMINATION SETTLEMENT # 13559 381.55
3/2/2010 TERMINATION SETTLEMENT # 12155 1,521.07
3/2/2010 TERMINATION SETTLEMENT # 13569 190.65
3/2/2010 TERMINATION SETTLEMENT # 13448 188.96
3/2/2010 TERMINATION SETTLEMENT # 13501 412.53
3/2/2010 TERMINATION SETTLEMENT # 13580 483.04
3/2/2010 TERMINATION SETTLEMENT # 13137 270.75
3/2/2010 TERMINATION SETTLEMENT # 13492 244.12
3/2/2010 TERMINATION SETTLEMENT # 13505 268.19
3/2/2010 TERMINATION SETTLEMENT # 13577 186.48
3/2/2010 TERMINATION SETTLEMENT # 13573 190.65
3/2/2010 TERMINATION SETTLEMENT # 13491 323.74
3/2/2010 TERMINATION SETTLEMENT # 13469 178.56
3/2/2010 TERMINATION SETTLEMENT # 13566 171.73
3/2/2010 TERMINATION SETTLEMENT # 13475 234.72
3/2/2010 TERMINATION SETTLEMENT # 13407 177.61
3/2/2010 TERMINATION SETTLEMENT # 13556 181.83
3/2/2010 TERMINATION SETTLEMENT # 13599 355.87
3/2/2010 TERMINATION SETTLEMENT # 10929 896.49
3/2/2010 TERMINATION SETTLEMENT # 13045 297.35
3/2/2010 TERMINATION SETTLEMENT # 12573 968.99
3/2/2010 TERMINATION SETTLEMENT # 13007 289.25
3/2/2010 TERMINATION SETTLEMENT # 13632 302.01
3/2/2010 TERMINATION SETTLEMENT # 12860 394.52
3/2/2010 #13111-VACATION SETTLEMENT 47.77
4/2/2010 TERMINATION SETTLEMENT # 13523 92.62
4/2/2010 TERMINATION SETTLEMENT # 13041 53.42
4/2/2010 TERMINATION SETTLEMENT # 13386 47.20
4/2/2010 TERMINATION SETTLEMENT # 13384 2.28
4/2/2010 TERMINATION SETTLEMENT # 13171 313.97
4/2/2010 TERMINATION SETTLEMENT # 13330 238.34
4/2/2010 TERMINATION SETTLEMENT # 13572 242.09
4/2/2010 TERMINATION SETTLEMENT # 12061 658.17
4/2/2010 TERMINATION SETTLEMENT # 13485 238.11
4/2/2010 TERMINATION SETTLEMENT # 13564 267.26
4/2/2010 TERMINATION SETTLEMENT # 13571 381.31
4/2/2010 TERMINATION SETTLEMENT # 13631 251.57
4/2/2010 TERMINATION SETTLEMENT # 13708 1,095.39
4/2/2010 TERMINATION SETTLEMENT # 13028 189.91
4/2/2010 # 11945 VACATION SETTLEMENT 183.56
4/2/2010 #11955 VACATION SETTLEMENT 64.29
6/2/2010 #10924-VACATION SETTLEMENT 150.24
6/2/2010 #11964-VACATION SETTLEMENT 120.42
6/2/2010 #13096-VACATION SETTLEMENT 87.10
6/2/2010 #12906-VACATION SETTLEMENT 60.76
6/2/2010 # 13514-TEMINATION SETTLEMENT 458.73
6/2/2010 # 13560-TEMINATION SETTLEMENT 242.08
6/2/2010 # 12099-TEMINATION SETTLEMENT 589.71
6/2/2010 # 13653-RESIGNATION SETTLEMENT 118.06
6/2/2010 # 13650-RESIGNATION SETTLEMENT 22.99
7/2/2010 TERMINATION SETTLEMENT # 13575 266.24
7/2/2010 TERMINATION SETTLEMENT # 12137 576.55
7/2/2010 TERMINATION SETTLEMENT # 13562 233.13
7/2/2010 TERMINATION SETTLEMENT # 13487 302.52
7/2/2010 TERMINATION SETTLEMENT # 13561 357.12
7/2/2010 TERMINATION SETTLEMENT # 10887 1,000.42
7/2/2010 TERMINATION SETTLEMENT # 13197 121.42
8/2/2010 TERMINATION SETTLEMENT # 13668 180.12
8/2/2010 TERMINATION SETTLEMENT # 13191 205.15
8/2/2010 TERMINATION SETTLEMENT # 13582 172.63
8/2/2010 TERMINATION SETTLEMENT # 13430 390.91
8/2/2010 TERMINATION SETTLEMENT # 13771 151.78
8/2/2010 TERMINATION SETTLEMENT # 13587 440.62
8/2/2010 TERMINATION SETTLEMENT # 13553 318.32
8/2/2010 TERMINATION SETTLEMENT # 13585 343.66
8/2/2010 TERMINATION SETTLEMENT # 13547 286.42
8/2/2010 TERMINATION SETTLEMENT # 13740 158.75
8/2/2010 TERMINATION SETTLEMENT # 13543 260.88
8/2/2010 TERMINATION SETTLEMENT # 13549 393.64
8/2/2010 TERMINATION SETTLEMENT # 13557 191.56
8/2/2010 TERMINATION SETTLEMENT # 13759 370.19
8/2/2010 TERMINATION SETTLEMENT # 13707 182.15
9/2/2010 TERMINATION SETTLEMENT # 13789 121.30
9/2/2010 TERMINATION SETTLEMENT # 13814 128.85
9/2/2010 TERMINATION SETTLEMENT # 13316 299.06
9/2/2010 TERMINATION SETTLEMENT # 13589 232.08
9/2/2010 TERMINATION SETTLEMENT # 13649 223.11
9/2/2010 TERMINATION SETTLEMENT # 13766 137.71
9/2/2010 TERMINATION SETTLEMENT # 13719 183.10
9/2/2010 TERMINATION SETTLEMENT # 12166 631.70
9/2/2010 #13184-VACATION SETTLEMENT 73.48
10/2/2010 VACATION SETTLMENT-#13042 93.22
10/2/2010 VACATION SETTLMENT-#13036 154.89
10/2/2010 VACATION SETTLMENT-#13512 84.65
10/2/2010 VACATION SETTLMENT-#13030 6.58
10/2/2010 TERMINATION SETTLEMENT # 10908 841.86
10/2/2010 TERMINATION SETTLEMENT # 13651 212.29
10/2/2010 TERMINATION SETTLEMENT # 13596 170.17
10/2/2010 TERMINATION SETTLEMENT # 13565 185.27
10/2/2010 TERMINATION SETTLEMENT # 13534 481.48
10/2/2010 TERMINATION SETTLEMENT # 12034 632.15
10/2/2010 TERMINATION SETTLEMENT # 12943 323.05
10/2/2010 TERMINATION SETTLEMENT # 13827 112.84
10/2/2010 TERMINATION SETTLEMENT # 11672 2,143.36
11/2/2010 TEMINATTION SETTLEMNET -#13816 125.24
11/2/2010 TEMINATTION SETTLEMNET -#12316 1,674.92
11/2/2010 TEMINATTION SETTLEMNET -#13141 49.65
11/2/2010 VACATION SETTLEMENT=#13642 123.27
11/2/2010 VACATION SETTLEMENT=# 13824 76.83
11/2/2010 VACATION SETTLEMENT=# 12258 55.49
14/02/2010 TERMINATION SETTLEMENT#13026 261.99
15/02/2010 #13275-VACATION SETTLEMENT 3.33
16/02/2010 #13714-VACATION SETTLEMENT 248.13
17/02/2010 #10855# RESIGNATION SETTLEMENT 829.68
18/02/2010 TERMINATION SETTLEMENT-13615 46.03
18/02/2010 TERMINATION SETTLEMENT-12382 423.92
18/02/2010 RESIGNATION SETTLEMENT-13016 183.20
18/02/2010 VACATION SETTLEMENT-13639 183.02
18/02/2010 # 13837-RESIGNATION SETTLEMTNT PAID 155.85
21/02/2010 # 13661-VAC.SETT 62.24
22/02/2010 #131321-VAC.SETT 136.74

How I can extract only the badge numbers in Column D with a formula?

Thanks in advance.

Jaleel
 
J

Jacob Skaria

Try the below formula in cell D1 and copy down as required...

=LOOKUP(99^99,--("0"&MID(B1,
MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789")),ROW($1:$10000))))
 
J

Jaleel

Jacob,

Wow! That's great! I have to work on lot of columns like this in many work
books entered by various persons without similarity. You saved my time and
effort. Thank you very much.

A personal question. Which country you belong to?

Jaleel
 
J

Jaleel

Hi,

Your name sounded like a Keralite. That's why I asked. I am proud of being
an Indian.

Cheers,

Jaleel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need difference in Hrs.mm.ss 3
create chart from daily data and add trend lines for each week 0
Keep unique values 1
Outlook 2010 can't find .pst file 1
Grouping Time in Pivots. 2
Changing Average - Ongoing for Excel 2007 2
Pivot Table Summarizing Quirk - Please Help 1
Check if a date comes before/after a range of dates 0

Top