Extract or pull certain text from entire worksheet Help !!

[skyh3ck]

Hello guys

I have a report where i extract passport number from a big raw data, all passport number is in a column, with name and other details,

now when i import data from word, the passport number does not come in single column, they are not vertically in same column, it comes in difference cell in each column, now i want only passport number in one single column, may be in a new sheet or file,

the indian passport number is of A1234567 format, first letter Alphabet and followed by 7 number, so want to extract this from entire sheet,

so in a single row there is only one passport number, but for each row it comes in different column

please help with a formula, the good thing is that the left cell of always contain word like "NUMBER" or "PASSPORT NUMBER"

thanks in advance, since it is very confidential data, i can not upload the file here

 

[pecoflyer]

Please post a sample file with some data and expected results

 

[skyh3ck]

a sample file attached,

data i wish to extract is highlighted, now i have this passport number in any of the cell in a row, they actually imported from a word file, which then converted text to column, there is no uniformity

the only good thing is that the number i wish to extract has adjacent cell mentioned as either "PASSPORT NUMBER" or "PASSPORT" and "NUMBER" broken in two cell, i just want the value of passport number from next cell in a single column,

 

[Chihiro]

If you want to extract these in single column, you could do something like...
In N1:
=INDEX(A1:L1,,LOOKUP(2,1/SEARCH("number",A1:L1),COLUMN(A1:L1))+1)

Copy down.

 

[Nebu]

Hi:

If you are interested in a Power Query Solution.

let
    Source = Excel.Workbook(File.Contents("C:\Users\nebusud\Downloads\Sample.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Sheet1_Sheet, {{"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}, "en-AU"),{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Position = Table.AddColumn(#"Merged Columns", "Position", each Text.PositionOf([Merged],"passport")+16),
    Test = Table.AddColumn(Position, "Test", each Text.Middle([Merged],[Position],Text.Length([Merged])-[Position])),
    #"Split Column by Delimiter" = Table.SplitColumn(Test, "Test", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Test.1", "Test.2", "Test.3", "Test.4", "Test.5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Position", "Test.2", "Test.3", "Test.4", "Test.5"}),
    #"Uppercased Text" = Table.TransformColumns(#"Removed Columns",{{"Test.1", Text.Upper, type text}})
in
    #"Uppercased Text"

Thanks