Extracting values in a column based on a unique value

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
Hello,

I am looking for some inputs on how i could extract a value from an adjacent column or row beneath based on a unique value in previous column. I have attached a picture for reference. Basically if column B has "AS3DW", it should like for value starting with 678 in column C and also check the row beneath same column because the value could exist in either of the rows and it should the full 10-digit number. In this case row 1643 coumn B has AS3DW and row 1644 column C has value starting with 678 so it should return a value of 678 000-3874-3899 in a separate column.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
in G1
=IF(ISNUMBER(SEARCH("AS3DW",B1)),MID(C1&C2,SEARCH("678",C1&C2)+4,13),"")
and copy down the column
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31

ADVERTISEMENT

Thank you. I used this formula and it gave me the last seven digits. Anyway i could get result with 678 appended. Also, a couple of things i would like to expand my search to include ND8 as well along with AS3DW. Once i extract these values. I want to look for duplicates, come up with unique records and then split those ranges into separate numbers in adjacent column.

Search on AS3DW and ND8 return below values
678 000-2200-2269

Looks for duplicates then split the ranges
678 000-2200
678 000-2201
678 000-2202....so forth
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Ah I didnt read the thread properly, I didnt realise you wanted the "678 " as well.

Just change the formula to

=IF(ISNUMBER(SEARCH("AS3DW",B1)),MID(C1&C2,SEARCH("678",C1&C2),17),"")

For the ND8 search too use

=IF(OR(ISNUMBER(SEARCH("AS3DW",B1)),ISNUMBER(SEARCH("ND8",B1))),MID(C1&C2,SEARCH("678",C1&C2),17),"")

though the sample data you supplied consists entirely of ND8 and AS3DW codes so there isn't a need to search for those phrases in your sample data.
 
Last edited:

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
Ah I didnt read the thread properly, I didnt realise you wanted the "678 " as well.

Just change the formula to

=IF(ISNUMBER(SEARCH("AS3DW",B1)),MID(C1&C2,SEARCH("678",C1&C2),17),"")

For the ND8 search too use

=IF(OR(ISNUMBER(SEARCH("AS3DW",B1)),ISNUMBER(SEARCH("ND8",B1))),MID(C1&C2,SEARCH("678",C1&C2),17),"")

though the sample data you supplied consists entirely of ND8 and AS3DW codes so there isn't a need to search for those phrases in your sample data.

This worked like a charm. You are awesome. Thank you so much:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

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