Skip to main content

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!

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.

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

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.

Reply


Terms & Conditions