Extracting values in a column based on a unique value

[Harry_1234]

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.

 

[Special-K99]

No picturer attached.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Harry_1234]

Here is the link to the file. Thanks
https://we.tl/N2QCJFRXXp

 

[Special-K99]

in G1
=IF(ISNUMBER(SEARCH("AS3DW",B1)),MID(C1&C2,SEARCH("678",C1&C2)+4,13),"")
and copy down the column

 

[Harry_1234]

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]

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.

 

[Harry_1234]

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