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 not able to work with a clean data set.
Example:
Jane Doe Jun 17, 2020 08:17 AM
Maricella De La Cruz Jun 18, 2021 08:18 AM
website_website.com Jul 23, 2020 09:21 AM
Amy SanMarcoDelRey Aug 17, 2022 11:24 PM
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.
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.
- You are AMAZING. Thank you. This is perfect!
- 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.
