Extracting a number from a text string and returning a third value

S

steelervince

Board Regular
Joined
May 29, 2007
Messages
83
Hello All!

I have one Excel file

In Column B (275 rows) I have a number (all of these numbers are unique)
In Column C (275 rows) I have a string of text which contains several numbers within the string (all of the text strings are unique)
In Column D (275 rows) I have a different number from Column B (all of these numbers are unique)

what I need:

a formula in Column E that
Looks at the number in column B
Searches the text string in Column C for presence of the number in Column B
If the number from Column B exists in the text string in Column C then, return the number from Column D otherwise return the text "N/A"

Any help would be appreciated.

V
 
Sort by date Sort by votes
I suggest (using PowerQuery)

CourseLearning ComponentsLP Course Number
117336118495 - Introduction to riding horses118499
117336118497 - Holding the reins and riding properly 118499
117336118496 - Caring for your horse118499
117336114838 - Stable cleaning118499
117336112179 - Apprentissage en ligne Normes sur le crédit spécialisé118499
117336117366 - Notions de base de l’évaluation immobiliere a l’intention des preteurs qualifiés 118499
117336117364 - Managing a fall from a horse properly118499
117162120187 - Racetrack ettiquette - Attestation120186
117159119640 - Learning from Difference118238
117159119641 - Diversity & Inclusion Course1118238
117159119642 - Diversity and Inclusion Course2118238
117159119643 - Blind Spots118238
117159119644 - The Difference of Being One118238
119085112129 - Fundamentals of Conversations119639
119085116486 - Essentials of Understanding119639
119085118918 - Essentials of Profiles119639
119085118725 - Essentials of Recording a TV Commercial119639
119085118731 - Essentials of Proper Interaction with a Customer119639
119085119038 - Help is Only 1 Call Away119639
119085114643 - Personal Best 119639
119085112123 - Conversations119639
119085112126 - Understanding Risk119639
119085112144 - Risk Mitigation119639
119085112131 - Working in a Fulfillment Center119639
119085112130 - Fulfillment Center Ettiquette119639
119085114647 - Customer Essentials 101119639
119085114648 - Customer Essentials 102119639
119085117399 - Conversations119639
119085118055 - Understanding Customer System - Being a Better Rep119639
119085118056 - Essentials of Hardwork119639
119085119339 - Creating a Work Order119639
118623118241
118624118083
118625118084
118839118085
118843118086
118885118087
118886118202
118887118203
118888118204
118889118205
118890118206
118891118207
118892118093
118893118094
118894118071
118895118110
118911118200
118912118216
118913118217
118914118252
118915118253
118916118254
118917118090
118922118004
118923118005
118924118006
118925118007
118927118261
118929118262
118930118263
118931118264
118932118265
118961113947
118962119258
118963119226
119009119227
119010119228
119013119229
119014119230
119054119231
119055118242
119057120299
119058120928
119059120882
119060118615
119061119399
119084119400
119085120194
119089120180
119090120100
119091119219
119092120425
119093118640
119094120563
119296120529
119297120362
119298121146
119299121147
119300120460
119301120461
119302120462
119303120463
119305120531
119306120528
119567120561
119568120562
119569121148
119570121130
119571121134
119572119981
119573119282
119574119586
119575119410
119576118428
119578120375
119579120382
119580120428
119581120429
119582118431
119583118645
119584118646
117763118267
117548118330
117549120412
117550118944
118546118592
118547119505
118548120467
118549119546
118502119313
115525119380
115526118613
115533120174
115537120069
117657120065
117321119766
117658119767
115536121093
115529119472
115530119474
115531117813
115532117815
115534118374
115694118375
115809118574
117762118575
117308118589
117309118590
117575118862
117578118863
117579118933
117580118938
115254118939
115170118940
115171118941
115172118942
115173118943
115167118910
115168118954
116384118721
116385118746
116387119012
116388119017
116389119234
116390119925
116391120293
116392120294
116394118694
116395118695
116396120555
116397121052
116398121053
116399120866
116400120289
116401119887
116380120168
117528119598
117529119664
117530119670
117531119283
117532119402
117646119340
117642119405
117643121125
117644121143
117334121144
117335121145
117330118833
117331118834
117332119684
117163119624
117164119051
117165119161
117177121116
117178121155
117179120883
117180121104
117181119746
117182119747
117183119803
117184119804
117185120176
117186120103
120214120104
120215120105
120778120106
120779120107
120780118214
120781118088
120782118089
120773118080
120774118081
120775118082
120776118255
120777118256
114963118092
115189118109
115785118803
115615118443
120396118641
120394118627
120405118651
120407118831
120404118832
120408118778
120406118661
120399119225
120400119182
120402119344
120401119360
120392119362
120395119363
120393119364
120398119311
120344119381
120345119382
120347119394
120349119408
120346119443
120348119434
120350119540
120338119508
120339119676
120341119712
120340119713
120342119478
120286119955
120287119956
120284119957
120275120046
120276121051
120277121035
120278121036
120279121037
120245121038
120266121039
120268121040
120267119890
120264119891
120265119892
120243119893
120242119894
120244119896
120246119897
120089119898
120090119899
120091119907
120088119908
120123119909
120121119910
120122119911
120096119645
120097118752
120163118430
120167119451
120165119398
120166119236
120164119940
120162119941
120161119942
120093119943
120094119944
120092119895
119997120074
119999120075
119996120076
119998120077
120000120108
120002120126
120001120254
119953121041
119951120932
119949121154
119950120156
119952120336
119809119756
119810120466
119812120856
119813120857
119737120858
119738120193
119742118078
119744119063
119745120833
119743120824
119739120879
119843
121102
121103
120389
120688
120787
120831
120129
120130
120131
120132
120154
120155
120205
120252
119764
119751
119752
120067
120053
120061
119523
119524
119261
119596
119691
119420
119421
118306
119590
120212
120213
120878
120490
119619
119620
119622
119623
120124
120125
120649
118244
121149
120102
118706
118707
120137
120098
120210
118079
119383
119365
119537
119000
118699
118788
119070
119083
118908
118909
119577
119585
119356
119475
119476
119972
119875
119876
119877
120160
120087
120095
120241
120274
120280
120288
120397
120403
120409
119753
118397
119635
120369
120753
118066
118100
120209
120080
120081
120040
120041
120042
120051
120520
120526
120509
120540
120762
120557
120761
120038
120039
120147
120148
119965
119966
118784
118743
118793
118735
119509
119510
118842
118622
119159
118282
120438
118700
120049
118064
118977
119976
118686
118794
118328
118329
119699
119700
118955
118956
118278
118620
117801
120497
119748
120329
120546
118775
118776
118072
120518
120519
118612
119018
118998
118354
119022
120044
119294
119295
119331
118302
121094
119878
119883
119637
119638
119629
119679
120869
118140
118879
118384
118073
118619
119250
119116
118215
118163
118618
119886
121083
118687
118611
119865
118883
120184
119470
121110
118835
119118
118447
120547
119019
120045
118113
119147
118355
119446
118301
118385
119888
119884
118166
118498
119253
119889
119260
120179
119359
118164
118165
119128
118300
118494
118689
118691
118692
118660
120024
119591
118587
118423
119314
119307
119814
119736
119948
119995
119404
118279
120771
120772
118437
120476
119959
118736
119385
120826
118483
120068
118741
120552
119220
119152
118720
118424
118688
120841
118311
120255
118773
118774
121114
120434
120435
119655
119730
120914
120311
120320
120912
119923
119931
119932
119259
119681
118664
118665
119037
120830
120851
120475
119885
120178
119881
119882
119794
119481
119064
119065
119066
119067
119208
119209
118435
118284
119111
118617
118249
118201
118343
119823
119824
120827
119386
118737
118543
119732
120927
119210
119211
119927
119929
119933
119934
119235
119697
119274
120312
120915
120913
120321
118666
118667
119042
120258
118649
119486
119155
119156
119157
119158
119826
119377
119437
120191
120192
120269
118576
117843
119533
119536
118577
120533
120926
119415
119828
119831
118393
118396
117842
118247
118245
118246
120447
118593
119561
116143
117324
117493
117505
116581
115891
116007
113859
117411
113744
115993
116164
116166
116204
116184
116206
117041
117042
113946
112211
117592
117322
117793
113702
113708
113705
117403
117407
117594
117061
115490
115491
115761
113711
117705
117743
117747
117717
113817
113743
117023
116205
116234
117001
117002
116165
116183
116203
117040
116149
115992
117039
115282
112155
117217
117218
117017
117150
115317
117073
115839
116214
116047
116053
116157
115733
115742
115768
115772
115773
115707
115469
115478
115479
115481
117106
115898
115899
115900
116191
116127
115374
113739
117342
117337
117338
117340
117341
117343
116358
117659
117280
117283
117188
117339
117344
117457
117458
117291
117699
113950
117013
116935
117200
116946
116980
117028
113863
113839
117221
117222
117371
117372
117601
117618
117629
117681
117553
117551
117284
117552
117543
117311
116948
116958
116962
116966
117109
113699
115197
115539
117702
115783
113940
117533
116371
116209
116403
117198
117736
115755
117582
115749
116311
116290
115822
117779
113871
113873
117160
117113
115465
116625
115263
113818
113819
113822
113823
116348
115583
115585
117156
117158
116407
116405
117497
117521
117525
116235
113704
116355
117587
113728
113727
117511
113729
113731
113942
115090
115093
113706
113707
113700
113701
113692
117369
115781
117663
116163
117600
117387
117591
117414
117459
117475
117479
117067
115210
115754
116246
116291
115186
115810
117068
115505
115748
115464
116402
117159
117133
117141
117142
117245
117349
117767
113872
113870
115862
113805
115262
113737
115937
113738
113736
115936
113807
113809
113811
115438
115283
116140
116217
115597
116404
117155
113941
117639
117032
116896
117157
116406
117730
117368
117402
115384
115665
104956
113952
116294
115575
115580
117768
116347
117613
117696
115909
116921
115954
115938
116622
116623
116924
117571
117615
116584
117346
115394
116848
113855
115368

C column can be splitted to number and text then as Joe suggested use vlookup
 
Last edited:
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You must log in or register to reply here.

Similar threads

C
  • Question
String Finding
Replies
4
Views
129
abdelfattah
A
C
  • Solved
formula to generate multilevel list numbering
2
Replies
14
Views
521
coffeenazi
C
H
  • Question
Search multiple cells for data "containing" a text string...then return the information from that cell
Replies
2
Views
595
Hat4Life
H
2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
1K
2BLUE4U
2BLUE4U
S
  • Question
How do I sum without knowing the range?
Replies
1
Views
103
sGraham24
S

Forum statistics

Threads
1,217,367
Messages
6,136,142
Members
449,994
Latest member
Rocky Mountain High

Share this page

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
Back
Top