Skip to main content

Hello!

I’m trying to separate 5 different numbers from a string, and they’re divided by commas.
I tried the LEFT, MID and RIGHT formulas but the numbers in the string can vary up to 4 digits per number. So, what I’m trying to find out is how can I separate every number between, before and after the commas.
The first row does exactly what I want. The second does not because the number of digits is different.

The string of numbers is entered via Zapier and can’t be changed.

Hi @Dylan_Wouters - the trick to this is finding the position of each of the commas (which varies per row given that the numbers can be up to 4 digits) then using this information to extract the relevant number. This is a messy solution, but it does work. I’ve done the first two numbers, hopefully you can extend out for the other numbers using the same pattern:

Not sure if you’re string has spaces in it, but I’ve removed these in my example so that the string I work on is just numbers and commas:

SUBSTITUTE(String, ' ', '')

{First Comma} is:
FIND(',', {Remove spaces})

{First Number} is:
MID({Remove spaces}, 1, {First Comma} - 1)

{Second Comma} is:
FIND(',', {Remove spaces}, {First Comma} + 1)

{Second Number} is:
MID({Remove spaces}, {First Comma} + 1, {Second Comma} - {First Comma} - 1)

JB

Terms & ConditionsAccessibility statement