Im trying to extract all the words in a single cell which contain following characters "ROWID&q-CodePudding

For example, if i have a cell which contains

ADDR_ROWID,LOC_ROWID,ADDR_LINE_1,ADDR_LINE_2,CITY,ST_PROV,CNTRY_CODE,POSTAL_CODE,STAT_IND,ADDR_TYPE_ROWID in cell A1

i want to extract ADDR_ROWID in B1, LOC_ROWID in C1 and ADDR_TYPE_ROWID in D1 cells respectively. is there any way possibly doing it?

CodePudding user response：

If you have access to VBA/Macros, then this snip-it might help;

Sub caller_getROWIDs()
    GetROWIDs [A1], [B1]
End Sub


Sub GetROWIDs(rInput As Range, rOutput As Range)

For Each SubString In Split(rInput.Value, ",")
    If InStr(SubString, "ROWID") Then
        rOutput.Value2 = SubString
        Set rOutput = rOutput.Offset(, 1)
    End If
Next

End Sub

CodePudding user response：

You can achive this with just excel formulas.

first you need to extract all the words in the cell which is separated by comma ",".

FORMULA 1.

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(SEQUENCE(,15)-SEQUENCE(,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) 1,1,1))*LEN(A1) 1,LEN(A1)))

Note: SEQUENCE(,15) is i am assuming there could be 15 word(I know there is 10 words since its only one cell imagine if you have more than 100), you can put any number as long as its not less then the actual word might be.

once you extract all the words from the cell then you need to filter your conditions which is if the letter contains "ROWID".

FORMULA 2.

=FILTER(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(SEQUENCE(,15)-SEQUENCE(,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) 1,1,1))*LEN(A1) 1,LEN(A1))),ISNUMBER(SEARCH("ROWID",TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(SEQUENCE(,15)-SEQUENCE(,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) 1,1,1))*LEN(A1) 1,LEN(A1))))))

You can just use the formula 2.