-2

For example, I have a list of data as per Column A. Is there a formula or way to clean up the data so that it will reflect the end product in Column B? Thank you.

Cell A1 contains 3 lines of data. Cell A2 contains 2 lines of data.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jx N
  • 1
  • Is the number important (e.g. to order the text), or are you just trying to discard the # and everything that follows? – Chronocidal May 20 '20 at 08:05
  • Hi @Chronocidal, nope the order of the text does not matter. I just want to remove the '#xxx' portion. Thank you. – Jx N May 20 '20 at 09:31

1 Answers1

1

If one has access to TEXTJOIN then formula in B1:

=TEXTJOIN(CHAR(10),1,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),"#"),"#","</s><s>")&"</s></t>","//s[position() mod 2 = 1]"))

Or, if you sure all values after # are numeric:

=TEXTJOIN(CHAR(10),1,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),"#"),"#","</s><s>")&"</s></t>","//s[.*0!=0]"))

Note: Enter through CtrlShiftEnter

enter image description here

For more FILTERXML "tricks", see here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Since the id's seem to have a fixed length of 4, wouldn't it be easier to use the LEFT function? – Geert Bellekens May 20 '20 at 07:55
  • @GeertBellekens, you are welcome to put an answer down =). It surely is valid, but I don't see how it would be shorter. – JvdV May 20 '20 at 07:57
  • Nah, not really my thing. It just seem much simpler with LEFT so I wondered if there was a reason why you didn't choose for that option. – Geert Bellekens May 20 '20 at 10:29
  • @GeertBellekens, the reason is that I'd have to use `FILTERXML` twice. e.g: `=TEXTJOIN(CHAR(10),1,LEFT(FILTERXML(""&SUBSTITUTE(A1,CHAR(10),"")&"","//s"),LEN(FILTERXML(""&SUBSTITUTE(A1,CHAR(10),"")&"","//s"))-5))` is a much longer construct =) – JvdV May 20 '20 at 10:35
  • 1
    Thanks, in the first image uploaded it wasn't clear there were more then one line in a single cell. Now it makes sense :) – Geert Bellekens May 20 '20 at 10:56
  • Hi @GeertBellekens Thank you for the help, it did work! However, can I check if is there a way to amend the formula if my Column A input contains a separator and spaces (e.g. Apples & Oranges#0001 etc)? – Jx N May 21 '20 at 06:04
  • @JxN I'm not the one who helped you, JvdV posted the answer :) – Geert Bellekens May 21 '20 at 06:16
  • 1
    @JxN, please update your question with representable sample data. The current answer works for the data given. If your data patterns differs, then please make sure to include this along with desired results. – JvdV May 21 '20 at 06:26
  • Ahh, thanks for pointing it out. @JvdV Thank you for looking into it and helping with my question! (: – Jx N May 21 '20 at 08:12
  • No worries, @JxN, but don't forget to update your question as without I can't help you any further. – JvdV May 21 '20 at 11:11