Substring before and after a comma

[JH40]

I'm trying to separate first and last name in a column into two columns. Seems like there should be a simple query expression for this. Does anyone know what that would be? The field is currently formatted as:

Doe, John (one column)

Need "Doe" in column 1 and "John" in column 2.

Thank you...

 

[gemma-the-husky]

you want the split function, which will divide the text into an array, based on whatever "split" you choose - in this case, a comma

then you can use the array.

 

[jdraw]

Another approach

x = "Doe, John"
column1 = Mid(x, 1, InStr(x, ",") - 1)
column2 = Mid(x, InStrRev(x, ",") + 2)

 

[JH40]

Thank you both so much!