Skip to main content

Grab "text" at certain comma break point

  • December 19, 2019
  • 3 replies
  • 43 views
A
  • Anonymous

Hello, I have a column with up to 4 values separated by commas. I am trying to take the values from each comma break point and place it in its own column. For example:

usgs, aauw, abernathiethomas, admiralty

CARTOGRAPHER_1 (Column) would display: usgs
CARTOGRAPHER_2 (Column) would display: aauw
CARTOGRAPHER_3 (Column) would display: abernathiethomas
CARTOGRAPHER_4 (Column) would display: admiralty

There will never be more than 4 columns / values /commas, but there will be a minimum of 1 value but not always 5 values.

I looked into the functions left, right, mid, search, len but can’t seem to make sense on how to make them work together to achieve this if at all posible.

Thanks for your help!

3 replies

J
Forum|alt.badge.img+18

I’ve done this before to get individual email addresses out of a lookup containing multiple email addresses.

I had to do it in stages -
First, strip off the first one, with a combination of LEFT and FIND, into its own cell.
Then strip off everything to the right of the first one, with a combination of RIGHT and FIND, into its own cell.
Then repeat the process with the addresses stripped off of the right side of the original - strip off the first one into its own cell, then strip off everything to the right of it into its own cell… and so on.

I believe that for 4 values, you’ll need something like 7 fields in total.

I also had to pepper the formulas with plenty of conditional checks for values to prevent errors.

Hopefully that made some sense and is helpful.

B
  • ben_pappas
  • New Participant
  • December 21, 2019

Were you able to figure this out? I need the same thing

J
Forum|alt.badge.img+18

Remove and keep first value (CARTOGRAPHER_1) in its own field:

IF(FIND(",",{CARTOGRAPHERS}&''),TRIM(SUBSTITUTE(LEFT({CARTOGRAPHERS}&'',SEARCH(",",{CARTOGRAPHERS}&'')),",","")),{CARTOGRAPHERS})

Remove and keep everything after the first value in its own field:

IF(FIND(",",{CARTOGRAPHERS}&''),TRIM(MID({CARTOGRAPHERS}&'',SEARCH(",",{CARTOGRAPHERS}&'')+1,500)))

Remove and keep the next value (CARTOGRAPHER_2) in its own field:

IF(FIND(",",{CARTOGRAPHERS_MINUS_1}&''),TRIM(SUBSTITUTE(LEFT({CARTOGRAPHERS_MINUS_1}&'',SEARCH(",",{CARTOGRAPHERS_MINUS_1}&'')),",","")),{CARTOGRAPHERS_MINUS_1})

Remove and keep everything after the second value in its own field:

IF(FIND(",",{CARTOGRAPHERS_MINUS_1}&''),TRIM(MID({CARTOGRAPHERS_MINUS_1}&'',SEARCH(",",{CARTOGRAPHERS_MINUS_1}&'')+1,500)))

Remove and keep next value (CARTOGRAPHER_3) in its own field:

IF(FIND(",",{CARTOGRAPHERS_MINUS_2}&''),TRIM(SUBSTITUTE(LEFT({CARTOGRAPHERS_MINUS_2}&'',SEARCH(",",{CARTOGRAPHERS_MINUS_2}&'')),",","")),{CARTOGRAPHERS_MINUS_2})

Remove and keep everything after the third value in its own field:

IF(FIND(",",{CARTOGRAPHERS_MINUS_2}&''),TRIM(MID({CARTOGRAPHERS_MINUS_2}&'',SEARCH(",",{CARTOGRAPHERS_MINUS_2}&'')+1,500)))

You can likely stop there… if you have more comma-separated values to split out, just continue the pattern.

O
B
Terms & ConditionsAccessibility statement