0

I have some cells with strings where some text is divided by a symbol.

I'd like to extract substrings by a delimiter, trim eventual blank spaces, add the hashtag char before each string and then add a space between them.

So from this: text1, text2, text3, text4 to #text1 #text2 #text3 #text4.

How do I do that? Thanks.

JvdV
  • 70,606
  • 8
  • 39
  • 70
antonioa7
  • 71
  • 1
  • 9
  • 2
    `=TEXTJOIN(" ",,"#"&TRIM(TEXTSPLIT(A1,",")))`, or use [`FILTERXML()`](https://stackoverflow.com/q/61837696/9758194) if no access to `TEXTSPLIT()` yet. – JvdV May 30 '22 at 12:55
  • thank you! how to use this also on powerapp with the matchall function? – antonioa7 May 30 '22 at 13:51
  • In PowerApps, I assume you would want to nest the appropriate alternatives; [`Split()`](https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-split), [`Trim()`](https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-trim) and [`Concat()`](https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-concatenate). If you must you can use a regular expression in the `MatchAll()` function, for example `\s*([^,]+)\s*(?:,|$)` – JvdV May 30 '22 at 13:57
  • I never used PowerApps, but it looks that you can refer to the `SubMatches` property of the `MatchAll()` function and nest this inside `Concat()`. I assume this would work. That raises the question: are you using Excel or PowerApps? Please be clear and concise in your question. – JvdV May 30 '22 at 14:10
  • Excel but i just realized i might need it on powerapp too :) thank you so much – antonioa7 May 30 '22 at 15:01
  • Or using : ="#"&SUBSTITUTE(A1,", "," #") – bosco_yip May 31 '22 at 06:39

0 Answers0