0

I am trying to extract the colour (Farve) from this text string. I can't seem to make the extraction either stop, or start the right place. Also, the length of the target extraction will vary, as colours of course are varying length name. The picture is of the target texted, wanted in the cell.

Hope some might be able to help, thank you. enter image description here

I have tried multiple solutions of Len, left, right combination as well as trim, index combination and regexetraxt.

Ike
  • 9,580
  • 4
  • 13
  • 29
Sebastian Moth
  • 1
  • For which tool - Excel or google-sheets - are you looking for a solution? If Excel: what is your excel version? – Ike Nov 29 '22 at 12:42
  • 2
    This screenshot is Google-Sheets. If that is the app of your choice, please stick to it and remove other tags from the question. Btw, if GS then use `REGEXEXTRACT()`. Something along the lines of `=REGEXEXTRACT(A2,"^Farve: (.+)$")` – JvdV Nov 29 '22 at 13:04

2 Answers2

0

If you have Excel 365 current channel, you can use this formula:

=LET(splitData,TEXTSPLIT(A2,":",CHAR(10)),
CHOOSECOLS(FILTER(splitData,CHOOSECOLS(splitData,1)="Farve"),2))

It first splits the data by colon and linebreak - this will return a two-column matrix. This matrix can be filtered by the first column to be "Farve" ...

Ike
  • 9,580
  • 4
  • 13
  • 29
  • Unfortunately i am not, thank you though. I am using Excel for mac v. 16.67 and Google sheets, not sure if there are any version of sheets. – Sebastian Moth Nov 29 '22 at 14:38
  • @SebastianMoth you need to specify via tag any excel version constraint, you can use Excel for Web that has more functionalities than the mac version, I have the same issue with my Mac. You can try [this solution](https://stackoverflow.com/questions/25316094/split-a-string-cell-in-excel-without-vba-e-g-for-array-formula/74092401#74092401) if you can use `LET` – David Leal Nov 29 '22 at 15:30
0

target it like:

=REGEXEXTRACT(A2; "(?i)Farve: (.+)")

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124