Forum Discussion
Solved
Excel - Need to split variable length names and dates
My issue is that I need to split names and dates that are all in the same column, but names are not always First / Last with a date. I need to be able to incorporate the solution into a macro. I am n...
Let's say the values are in A2 and down.
In B2:
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",255),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-4),255))
In C2:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-4),255,255))
Fill down.
I am not able to use the text-to-columns function to split these because of the variable amount of spaces and characters for each entry. Some users have very long names with multiple spaces, and some entries are submitted from an email address or web portal entry that may contain numeric digits.