0

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?

andrewJames
  • 19,570
  • 8
  • 19
  • 51
Siddhartha TM
  • 13
  • 2
  • You may have a look here: https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7 … as formula: TEXTSPLIT or have a look here: https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68 .. there is also a solution with PowerQuery – Anonymous Jul 11 '22 at 00:08

4 Answers4

2

Try FILTERXML():

enter image description here

Formula in B1:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[substring(., string-length(.)-5) = '_ROWID']"))

We basically created the missing ends-with() xpath function. See here for a more in-depth explaination why this works.

Or; with the newest versions of Excel you can even use:

=LET(X,TEXTSPLIT(A1,","),FILTER(X,RIGHT(X,6)="_ROWID",""))

Note: The latter is currently case-insensitive where the FILTERXML() option is case-sensitive.

JvdV
  • 70,606
  • 8
  • 39
  • 70
1

So a method with find(), iferror() and if(), perhaps simpler than some:

enter image description here

Formula in cell B1:

IF(IFERROR(FIND("ADDR_ROWID",A1,1),0)>0,"ADDR_ROWID","")

The others just change the text in the find() & if().

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
0

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
bn_ln
  • 1,648
  • 1
  • 6
  • 13
0

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.

Example

Saif Ulislam
  • 56
  • 5